from flask import Flask, request, make_response, jsonify, send_from_directory, abort from flask_httpauth import HTTPBasicAuth import logging from datetime import datetime, timedelta import pandas as pd from dateutil import parser from configparser import ConfigParser from flask_mysqldb import MySQL from dateutil import parser, tz logging.basicConfig(filename='/var/www/html/logs/bulk-sms-delivery.log', filemode='a', format='%(asctime)s %(name)s %(levelname)s %(message)s', level=logging.INFO) #Read config.ini file config_object = ConfigParser() config_object.read("config.ini") #Get info live_hellio_info = config_object["live_hellio"] authinfo = config_object["auth"] app = Flask(__name__) app.config['MYSQL_HOST'] = str(live_hellio_info["host"]) app.config['MYSQL_USER'] = str(live_hellio_info["user"]) app.config['MYSQL_PASSWORD'] = str(live_hellio_info["password"]) app.config['MYSQL_DB'] = str(live_hellio_info["db"]) app.config['UPLOAD_EXTENSIONS'] = ['.txt', '.csv', '.xlsx'] mysql = MySQL(app) auth = HTTPBasicAuth() USER_DATA = { str(authinfo["username"]): str(authinfo["password"]) } @auth.verify_password def verify(username, password): if not (username and password): return False return USER_DATA.get(username) == password @app.route('/bulk/stats', methods=['POST', 'GET']) @auth.login_required def bulk_sms(): if request.method == 'POST': req = request.get_json() status = str(req['status']) conn_1 = mysql.connection req = request.get_json() request_start_date = req['start_date'] request_end_date = req['end_date'] today = datetime.utcnow().date() if not request_start_date.strip() or not request_end_date.strip(): request_start_date = datetime(today.year, today.month, today.day, tzinfo=tz.tzutc()) request_end_date = request_start_date + timedelta(days=1, microseconds=-1) request_start_date = request_start_date.isoformat() request_start_date = request_start_date.replace(request_start_date[19:], '') request_start_date = request_start_date.replace('T', " ") request_start_date = request_start_date.replace(request_start_date[11:], '00:00:00') request_end_date = request_end_date.isoformat() request_end_date = request_end_date.replace(request_end_date[19:], '') request_end_date = request_end_date.replace('T', " ") request_end_date = request_end_date.replace(request_end_date[11:], '23:59:59') else: request_start_date = parser.parse(req['start_date']).isoformat() request_end_date = parser.parse(req['end_date']).isoformat() request_start_date = request_start_date.replace('T', " ") request_start_date = request_start_date.replace(request_start_date[11:], '00:00:00') request_end_date = request_end_date.replace('T', " ") request_end_date = request_end_date.replace(request_end_date[11:], '23:59:59') logging.info(f"[start date:{request_start_date}] [end date:{request_end_date}] [status:{status}]") try: if status == "all": # total number of bulk sms total_data = pd.read_sql("SELECT status, count(id) as count FROM user_jobs WHERE status in ('done', 'failed', 'starting', 'pending', 'cancelled', 'Awaiting approval') AND sched_datetime between '{}' and '{}' GROUP BY status".format(request_start_date, request_end_date), conn_1) total_data['total_sms_count'] = total_data['count'].sum() total_bulk_breakdown = pd.read_sql("SELECT type, status, count(id) as count FROM user_jobs WHERE type in ('bulk', 'p-bulk', 'group' ,'p_group') AND sched_datetime between '{}' and '{}' GROUP BY type, status".format(request_start_date, request_end_date), conn_1) if not len(total_data): total_data = 0 logging.info(f"total sms count:{total_data}") else: logging.info(f"total sms count:{total_data.head(1)}") total_sms_count = total_data.to_dict(orient='records') if not len(total_bulk_breakdown): total_bulk_breakdown = 0 logging.info(f"total breakdown:{total_bulk_breakdown}") else: logging.info(f"total breakdown:{total_bulk_breakdown.head(1)}") total_bulk_breakdown = total_bulk_breakdown.to_dict(orient='records') return make_response(jsonify(code=200, total_sms_count=total_sms_count, total_bulk_breakdown=total_bulk_breakdown), 200) elif status == "done": # total number of bulk sms total_done = pd.read_sql("SELECT user_id, username, type, sender, message, sms_count, total_traffic, message_type, sched_datetime, group_ids, file_path FROM user_jobs WHERE status='{}' AND sched_datetime between '{}' and '{}' ORDER BY sched_datetime desc".format(status, request_start_date, request_end_date), conn_1) if not len(total_done): return make_response(jsonify(code=404, error='No results'), 200) logging.info(f"done:{total_done.head(1)}") total_done = total_done.to_dict(orient='records') return make_response(jsonify(code=200, total_done=total_done), 200) elif status == "failed": # total number of bulk sms total_failed = pd.read_sql("SELECT user_id, username, type, sender, message, sms_count, total_traffic, message_type, sched_datetime, group_ids, file_path FROM user_jobs WHERE status='{}' AND sched_datetime between '{}' and '{}' ORDER BY sched_datetime desc".format(status, request_start_date, request_end_date), conn_1) if not len(total_failed): return make_response(jsonify(code=404, error='No results'), 200) logging.info(f"failed:{total_failed.head(1)}") total_failed = total_failed.to_dict(orient='records') return make_response(jsonify(code=200, total_failed=total_failed), 200) elif status == "starting": # total number of bulk sms total_starting = pd.read_sql("SELECT user_id, username, type, sender, message, sms_count, total_traffic, message_type, sched_datetime, group_ids, file_path FROM user_jobs WHERE status='{}' AND sched_datetime between '{}' and '{}' ORDER BY sched_datetime desc".format(status, request_start_date, request_end_date), conn_1) if not len(total_starting): return make_response(jsonify(code=404, error='No results'), 200) logging.info(f"starting:{total_starting.head(1)}") total_starting = total_starting.to_dict(orient='records') return make_response(jsonify(code=200, total_starting=total_starting), 200) elif status == "cancelled": # total number of bulk sms total_cancelled = pd.read_sql("SELECT user_id, username, type, sender, message, sms_count, total_traffic, message_type, sched_datetime, group_ids, file_path FROM user_jobs WHERE status='{}' AND sched_datetime between '{}' and '{}' ORDER BY sched_datetime desc".format(status, request_start_date, request_end_date), conn_1) if not len(total_cancelled): return make_response(jsonify(code=404, error='No results'), 200) logging.info(f"starting:{total_cancelled.head(1)}") total_cancelled = total_cancelled.to_dict(orient='records') return make_response(jsonify(code=200, total_cancelled=total_cancelled), 200) elif status == "Awaiting approval": # total number of bulk sms total_awaiting = pd.read_sql("SELECT user_id, username, type, sender, message, sms_count, total_traffic, message_type, sched_datetime, group_ids, file_path FROM user_jobs WHERE status='{}' AND sched_datetime between '{}' and '{}' ORDER BY sched_datetime desc".format(status, request_start_date, request_end_date), conn_1) if not len(total_awaiting): return make_response(jsonify(code=404, error='No results'), 200) logging.info(f"Awaiting approval:{total_awaiting.head(1)}") total_awaiting = total_awaiting.to_dict(orient='records') return make_response(jsonify(code=200, total_awaiting=total_awaiting), 200) else: logging.info("No search parameters") return make_response(jsonify(code=404, error='No results'), 200) except Exception as e: logging.info(e) return make_response(jsonify(code=404, error='No results'), 200) @app.route('/user/stats', methods=['POST', 'GET']) @auth.login_required def user_stats(): if request.method == 'POST': req = request.get_json() status = str(req['status']) username = str(req['username']) cnx = mysql.connection req = request.get_json() request_start_date = req['start_date'] request_end_date = req['end_date'] today = datetime.utcnow().date() if not request_start_date.strip() or not request_end_date.strip(): request_start_date = datetime(today.year, today.month, today.day, tzinfo=tz.tzutc()) request_end_date = request_start_date + timedelta(days=1, microseconds=-1) request_start_date = request_start_date.isoformat() request_start_date = request_start_date.replace(request_start_date[19:], '') request_start_date = request_start_date.replace('T', " ") request_start_date = request_start_date.replace(request_start_date[11:], '00:00:00') request_end_date = request_end_date.isoformat() request_end_date = request_end_date.replace(request_end_date[19:], '') request_end_date = request_end_date.replace('T', " ") request_end_date = request_end_date.replace(request_end_date[11:], '23:59:59') else: request_start_date = parser.parse(req['start_date']).isoformat() request_end_date = parser.parse(req['end_date']).isoformat() request_start_date = request_start_date.replace('T', " ") request_start_date = request_start_date.replace(request_start_date[11:], '00:00:00') request_end_date = request_end_date.replace('T', " ") request_end_date = request_end_date.replace(request_end_date[11:], '23:59:59') logging.info(f"[start date:{request_start_date}] [end date:{request_end_date}] [username:{username}] [status:{status}]") try: if username.strip() and status == "all": # total number of bulk sms total_data = pd.read_sql("SELECT status, count(id) as count FROM user_jobs WHERE username='{}' AND status in ('done', 'failed', 'starting', 'pending', 'cancelled', 'Awaiting approval') AND sched_datetime between '{}' and '{}' GROUP BY status".format(username, request_start_date, request_end_date), cnx) total_data['total_sms_count'] = total_data['count'].sum() total_bulk_breakdown = pd.read_sql("SELECT type, status, count(id) as count FROM user_jobs WHERE username='{}' AND type in ('bulk', 'p-bulk', 'group' ,'p_group') AND sched_datetime between '{}' and '{}' GROUP BY type, status".format(username, request_start_date, request_end_date), cnx) if not len(total_data): total_data = 0 logging.info(f"total sms count:{total_data}") else: logging.info(f"total sms count:{total_data.head(1)}") total_sms_count = total_data.to_dict(orient='records') if not len(total_bulk_breakdown): total_bulk_breakdown = 0 logging.info(f"total breakdown:{total_bulk_breakdown}") else: logging.info(f"total breakdown:{total_bulk_breakdown.head(1)}") total_bulk_breakdown = total_bulk_breakdown.to_dict(orient='records') return make_response(jsonify(code=200, total_sms_count=total_sms_count, total_bulk_breakdown=total_bulk_breakdown), 200) elif username.strip() and status == "done": # total number of bulk sms total_done = pd.read_sql("SELECT user_id, type, sender, message, sms_count, total_traffic, message_type, sched_datetime, group_ids, file_path FROM user_jobs WHERE username='{}' AND status='{}' AND sched_datetime between '{}' and '{}' ORDER BY sched_datetime desc".format(username, status, request_start_date, request_end_date), cnx) logging.info(f"done:{total_done.head(1)}") if not len(total_done): return make_response(jsonify(code=404, error='No results'), 200) total_done = total_done.to_dict(orient='records') return make_response(jsonify(code=200, total_done=total_done), 200) elif username.strip() and status == "failed": # total number of bulk sms total_failed = pd.read_sql("SELECT user_id, type, sender, message, sms_count, total_traffic, message_type, sched_datetime, group_ids, file_path FROM user_jobs WHERE username='{}' AND status='{}' AND sched_datetime between '{}' and '{}' ORDER BY sched_datetime desc".format(username, status, request_start_date, request_end_date), cnx) logging.info(f"failed:{total_failed.head(1)}") if not len(total_failed): return make_response(jsonify(code=404, error='No results'), 200) total_failed = total_failed.to_dict(orient='records') return make_response(jsonify(code=200, total_failed=total_failed), 200) elif username.strip() and status == "starting": # total number of bulk sms total_starting = pd.read_sql("SELECT user_id, type, sender, message, sms_count, total_traffic, message_type, sched_datetime, group_ids, file_path FROM user_jobs WHERE username='{}' AND status='{}' AND sched_datetime between '{}' and '{}' ORDER BY sched_datetime desc".format(username, status, request_start_date, request_end_date), cnx) logging.info(f"starting:{total_starting.head(1)}") if not len(total_starting): return make_response(jsonify(code=404, error='No results'), 200) total_starting = total_starting.to_dict(orient='records') return make_response(jsonify(code=200, total_starting=total_starting), 200) elif username.strip() and status == "cancelled": # total number of bulk sms total_cancelled = pd.read_sql("SELECT user_id, type, sender, message, sms_count, total_traffic, message_type, sched_datetime, group_ids, file_path FROM user_jobs WHERE username='{}' AND status='{}' AND sched_datetime between '{}' and '{}' ORDER BY sched_datetime desc".format(username, status, request_start_date, request_end_date), cnx) logging.info(f"cancelled:{total_cancelled.head(1)}") if not len(total_cancelled): return make_response(jsonify(code=404, error='No results'), 200) total_cancelled = total_cancelled.to_dict(orient='records') return make_response(jsonify(code=200, total_cancelled=total_cancelled), 200) elif username.strip() and status == "Awaiting approval": # total number of bulk sms total_awaiting = pd.read_sql("SELECT user_id, type, sender, message, sms_count, total_traffic, message_type, sched_datetime, group_ids, file_path FROM user_jobs WHERE username='{}' AND status='{}' AND sched_datetime between '{}' and '{}' ORDER BY sched_datetime desc".format(username, status, request_start_date, request_end_date), cnx) logging.info(f"Awaiting approval:{total_awaiting.head(1)}") if not len(total_awaiting): return make_response(jsonify(code=404, error='No results'), 200) total_awaiting = total_awaiting.to_dict(orient='records') return make_response(jsonify(code=200, total_awaiting=total_awaiting), 200) else: logging.info("No search parameters") return make_response(jsonify(code=404, error='No results'), 200) except Exception as e: logging.info(f"failed with error {e}") return make_response(jsonify(code=500, error='search api failed'), 200) @app.route('/campaign/name/', methods=['POST', 'GET']) @auth.login_required def campaignName(): if request.method == 'POST': req = request.get_json() user_id = req['user_id'] user_id = int(user_id) username = str(req['username']) cur = mysql.connection try: campaign_data = pd.read_sql("SELECT name FROM user_jobs WHERE user_id={} AND username='{}'".format(user_id, username), cur) results = campaign_data['name'].to_list() logging.info(f"name = {results[0]}") except Exception as e: logging.info(e) return make_response(jsonify(code=404, error='no campaign name found for user'), 200) if not results: logging.info(f"No campaign name for {username}") return make_response(jsonify(code=404, error='no campaign name found for user'), 200) return make_response(jsonify(code=200, campaign_name=results),200) if __name__ =='__main__': app.run(host='0.0.0.0')