from flask_restful import Resource, reqparse from flask import jsonify, make_response from app import db import requests import datetime import constants as const import pandas as pd from collections import OrderedDict from datetime import date import time class GetCallLogs(Resource): def post(self): parser = reqparse.RequestParser() parser.add_argument('account_number') parser.add_argument("start_date", default=datetime.date.today().strftime('%Y-%m-%d')) parser.add_argument("end_date", default=datetime.date.today().strftime('%Y-%m-%d')) parser.add_argument('call_direction', default=0) args = parser.parse_args() account_number = args['account_number'] start_date = args['start_date'] end_date = args['end_date'] call_direction = args['call_direction'] columns = ['ID', 'Source', 'Incoming DID', 'Destination', 'Start Date', 'Start Time', 'Answered Time', 'End Time', 'Duration (in sec)', 'Call Rate / min (in GBP)', 'Call Cost (in GBP)', 'Area Code', 'Destination Details', 'Call Direction'] inbound_calls = {} inbound_calls = OrderedDict() outbound_calls = {} l_source = [] l_incoming_did = [] l_destination = [] l_start_date = [] l_start_time = [] l_answered_time = [] l_end_time = [] l_duration = [] l_call_rate = [] l_call_cost = [] l_area_code = [] l_destination_details = [] l_call_direction = [] call_logs = db.session.execute(const.SQL_CALL_LOGS_XLS.format(start_date, end_date, account_number)) for (call_from, call_to_did, call_to, call_start_date, call_get_time, call_start_time, call_end_time, call_duration, call_rate_per_min, call_cost, call_area_code, call_location, status) in call_logs: l_source.append(str(call_from)) l_incoming_did.append(str(call_to_did)) l_destination.append(str(call_to)) l_start_date.append(str(call_start_date)) l_start_time.append(str(call_get_time)) l_answered_time.append(str(call_start_time)) l_end_time.append(str(call_end_time)) l_duration.append(str(call_duration)) l_call_rate.append(str(call_rate_per_min)) l_call_cost.append(str(call_cost)) l_area_code.append(str(call_area_code)) l_destination_details.append(str(call_location)) if status == 1: l_call_direction.append("INBOUND") elif status == 2: l_call_direction.append("OUTBOUND") elif status == 3: l_call_direction.append("EXTENSION") inbound_calls['Source'] = l_source inbound_calls['Incoming DID'] = l_incoming_did inbound_calls['Destination'] = l_destination inbound_calls['Start Date'] = l_start_date inbound_calls['Start Time'] = l_start_time inbound_calls['Answered Time'] = l_answered_time inbound_calls['End Time'] = l_end_time inbound_calls['Duration (in sec)'] = l_duration inbound_calls['Call Rate / min (in GBP)'] = l_call_rate inbound_calls['Call Cost (in GBP)'] = l_call_cost inbound_calls['Area Code'] = l_area_code inbound_calls['Destination Details'] = l_destination_details inbound_calls['Call Direction'] = l_call_direction print(inbound_calls) df_call_logs = pd.DataFrame(inbound_calls) writer = pd.ExcelWriter(const.CALL_LOG_XL_PATH, 'output.xlsx') df_call_logs.to_excel(writer, index=False, sheet_name='Call Logs') # save the excel writer.save() print('Call logs successfully written to Excel File.') class GetCallLogsUsingPandas(Resource): def post(self): parser = reqparse.RequestParser() parser.add_argument("account_number") parser.add_argument("start_date", default=datetime.date.today().strftime('%Y-%m-%d')) parser.add_argument("end_date", default=datetime.date.today().strftime('%Y-%m-%d')) parser.add_argument("call_direction", default=0) args = parser.parse_args() account_number = args['account_number'] start_date = args['start_date'] end_date = args['end_date'] call_direction = args['call_direction'] # print(str(account_number)) print(str(call_direction)) # print(str(start_date)) columns = ['ID', 'Source', 'Incoming DID', 'Destination', 'Start Date', 'Start Time', 'Answered Time', 'End Time', 'Duration (in sec)', 'Call Rate / min (in GBP)', 'Call Cost (in GBP)', 'Area Code', 'Destination Details', 'Call Direction'] print('Preparing to fetch data from table') epoch_time = int(time.time()) today = date.today() file_name = 'cdr_' + str(today) + str(epoch_time) + '.xlsx' # file_name = 'html/api.quiits.com/static/' + file_name # if int(call_direction) == 0: # call_direction = '' # elif int(call_direction) == 1: # call_direction = '1' # elif int(call_direction) == 2: # call_direction = '2' print(str(const.SQL_CALL_LOGS_XLS.format(start_date, end_date, account_number, ''))) sql_df = pd.read_sql( const.SQL_CALL_LOGS_XLS.format(start_date, end_date, account_number, ''), con=db.engine ) # print(sql_df) # print(sql_df['Destination']) # print(sql_df.info()) # sql_df['Destination'] = sql_df['Destination'].astype() writer = pd.ExcelWriter( const.CALL_LOG_XL_PATH + file_name, engine='xlsxwriter' ) sql_df.to_excel(writer, header=True, index=False, sheet_name='Call Logs') # # sql_df.to_excel(writer, header=True, index=False) # Get the xlsxwriter workbook and worksheet objects workbook = writer.book worksheet = writer.sheets['Call Logs'] # Adding formats to the sheet source_format = workbook.add_format({'num_format': '0'}) worksheet.set_column('A1:A', 18, source_format) worksheet.set_column('B1:B', 18, source_format) worksheet.set_column('C1:C', 18, source_format) worksheet.set_column('H1:H', 18, source_format) worksheet.set_column('K1:K', 18, source_format) time_format = workbook.add_format({'num_format': 'hh:mm:ss'}) worksheet.set_column('E1:E', 14, time_format) worksheet.set_column('F1:F', 14, time_format) worksheet.set_column('G1:G', 14, time_format) date_format = workbook.add_format({'num_format': 'yyyy/mm/dd'}) worksheet.set_column('D1:D', 14, date_format) rate_per_min_format = workbook.add_format({'num_format': '0.000'}) worksheet.set_column('I1:I', 21, rate_per_min_format) call_cost_format = workbook.add_format({'num_format': '0.00000'}) worksheet.set_column('J1:J', 18, call_cost_format) text_format = workbook.add_format({'num_format': '@'}) worksheet.set_column('L1:L', 18, text_format) worksheet.set_column('M1:M', 18, text_format) writer.save() print('Call logs successfully written to Excel File.') return jsonify(status=1, message='Success', result={"file_link": "https://api.quiits.com/templates/" + file_name}) # except Exception as e: # print("Exception !!!") # print(str(e)) # return jsonify(status=0, message='Fail', result='Something went wrong !!!')