def login_payment_info():
session = create_session()
a=create_db_classes()
x=a[0]
y=a[1]
login_results =session.query(x.TRX_HOUR, func.count(x.TRX_HOUR)).filter(x.FAILED=="N").group_by(x.TRX_HOUR).all()
payment_results=session.query(y.TRX_HOUR, func.count(y.TRX_HOUR)).filter(y.FAILED=="N").group_by(y.TRX_HOUR).all()
# Create lists from the query results
hours = [result[0] for result in login_results]
lgn_count = [int(result[1]) for result in login_results]
hours1 = [result[0] for result in payment_results]
pay_count = [int(result[1]) for result in payment_results]
# Generate the plot trace
trace1 = {
"x": hours,
"y": lgn_count,
"type": "scatter"
}
trace2 = {
"x": hours1,
"y": pay_count,
"type": "scatter"
}
lists=[trace1,trace2]
return jsonify(results = lists)
def payment_mob_info():
session = create_session()
x=create_db_classes()[1]
success_results_mob = session.query(x.TRX_HOUR, func.count(x.TRX_HOUR)).\
filter(x.FAILED=="N").\
filter(x.SRC_SYS=="COLDAPP").\
group_by(x.TRX_HOUR).all()
print(success_results_mob)
failure_results_mob = session.query(x.TRX_HOUR, func.count(x.TRX_HOUR)).\
filter(x.FAILED=="Y").\
filter(x.SRC_SYS=="COLDAPP").\
group_by(x.TRX_HOUR).all()
print(failure_results_mob)
# Create lists from the query results
hours_mob = [result[0] for result in success_results_mob]
suc_count_mob = [int(result[1]) for result in success_results_mob]
hours1_mob = [result[0] for result in failure_results_mob]
fail_count_mob = [int(result[1]) for result in failure_results_mob]
# Generate the plot trace
trace1 = {
"x": hours_mob,
"y": suc_count_mob,
"type": "bar",
"name" : "SUCCESS"
}
trace2 = {
"x": hours1_mob,
"y": fail_count_mob,
"type": "bar",
"name" : "FAILURE"
}
lists=[trace1,trace2]
return jsonify(results = lists)
def failure_data_info():
session = create_session()
x=create_db_classes()[0]
success_results_web = session.query(x.TRX_DATETIME, x.API_NAME,x.RESPONSE_TIME,x.SRC_SYS,x.LOGINID,x.SERVER_ID).\
filter(x.FAILED=="Y").\
order_by(x.TRX_DATETIME).limit(400).\
all()
df = pd.DataFrame.from_records(success_results_web, columns =["TIMESTAMP","API_NAME","RESPONSE_TIME","SRC_SYS","LOGINID","SERVER_ID"])
lists=df.to_dict('records')
return jsonify(results = lists)
def insert_login_records(filepath):
session = create_session()
mod_df = create_clean_df(filepath)
for index, row in mod_df.iterrows():
print(index)
# get restaurant type from df
SRC_SYS = row['SRC_SYS']
SUB_SYS = row['SUB_SYS']
SERVER_ID = row['SERVER_ID']
NAME = row['NAME']
PARENT_NAME = row['PARENT_NAME']
MOBILE_NUMBER = row['MOBILE_NUMBER']
WIRELESS_BAN = row['WIRELESS_BAN']
UVERSE_BAN = row['UVERSE_BAN']
CSR_ID = row['CSR_ID']
START_TIME = datetime.strptime(time_microsec(row['START_TIME']),
"%d-%b-%y %H.%M.%S.%f %p")
TX_TIME = row['TX_TIME']
FAILED = row['FAILED']
SESSION_CODE = row['SESSION_CODE']
SLID = row['SLID']
BTN = row['BTN']
DTV_BAN = row['DTV_BAN']
AGENT_ID = row['CSR_ID']
DATE = START_TIME.date()
HOUR = START_TIME.time().strftime('%H')
mod_df.loc[index, 'TRX_HOUR'] = HOUR
mod_df.loc[index, 'TRX_DATE'] = DATE
login_info = logininfo(SRC_SYS=SRC_SYS,
SUB_SYS=SUB_SYS,
SERVER_ID=SERVER_ID,
API_NAME=NAME,
METHOD_NAME=PARENT_NAME,
MOBILE_NUMBER=MOBILE_NUMBER,
WIRELESS_BAN=WIRELESS_BAN,
WIRELINE_BAN=BTN,
UVERSE_BAN=UVERSE_BAN,
DTV_BAN=DTV_BAN,
LOGINID=SLID,
TRX_DATE=DATE,
TRX_HOUR=HOUR,
TRX_DATETIME=START_TIME,
SESSION_ID=SESSION_CODE,
FAILED=FAILED,
RESPONSE_TIME=TX_TIME,
AGENT_ID=AGENT_ID)
session.add(login_info)
session.commit()
def login_info():
session = create_session()
x=create_db_classes()[0]
results = session.query(x.TRX_HOUR, func.count(x.TRX_HOUR)).\
group_by(x.TRX_HOUR).all()
mob_results = session.query(x.TRX_HOUR, func.count(x.TRX_HOUR)).\
filter(x.SRC_SYS=="COLDAPP").\
group_by(x.TRX_HOUR).all()
web_results = session.query(x.TRX_HOUR, func.count(x.TRX_HOUR)).\
filter(x.SRC_SYS=="OLAM").\
group_by(x.TRX_HOUR).all()
# Create lists from the query results
hours = [result[0] for result in results]
count = [int(result[1]) for result in results]
hours1 = [result[0] for result in mob_results]
count1 = [int(result[1]) for result in mob_results]
hours2 = [result[0] for result in web_results]
count2 = [int(result[1]) for result in web_results]
# Generate the plot trace
trace1 = {
"x": hours,
"y": count,
"type": "scatter",
"mode": "lines+markers",
"name" : "OVERALL",
"line": {"color" :"#17BECF"}
}
trace2 = {
"x": hours1,
"y": count1,
"type": "bar",
"name" : "MOBILE",
"mode":'lines+markers'
}
trace3 = {
"x": hours2,
"y": count2,
"type": "bar",
"name" :"DESKTOP",
"mode":'lines+markers'
}
lists=[trace1,trace2,trace3]
return jsonify(results = lists)
def agent_mob_info():
session = create_session()
x=create_db_classes()[2]
success_results_mob = session.query(x.TRX_HOUR, func.count(x.TRX_HOUR)).\
filter(x.FAILED=="N").\
filter(x.SRC_SYS=="COLDAPP").\
group_by(x.TRX_HOUR).all()
# Create lists from the query results
hours_mob = [result[0] for result in success_results_mob]
suc_count_mob = [int(result[1]) for result in success_results_mob]
# Generate the plot trace
trace1 = {
"x": hours_mob,
"y": suc_count_mob,
'mode': 'markers'
}
lists=[trace1]
return jsonify(results = lists)
def agent_search_info():
session = create_session()
x=create_db_classes()[2]
success_results = session.query(x.TRX_HOUR, func.count(x.TRX_HOUR)).\
filter(x.FAILED=="N").\
group_by(x.TRX_HOUR).all()
# Create lists from the query results
hours = [result[0] for result in success_results]
suc_count = [int(result[1]) for result in success_results]
# Generate the plot trace
trace1 = {
"y": hours,
"x": suc_count,
'type': 'bar',
"orientation": "h"
}
lists=[trace1]
return jsonify(results = lists)