def __init__(self):
self.env = env
self.dbuser = env.postgreSql_dbuser
self.dbpswd = env.postgreSql_dbpswd
self.dbhost = env.postgreSql_dbhost
self.dbport = env.postgreSql_dbport
self.dbname = env.postgreSql_dbname
self.tableconversation = env.postgreSql_table_conversation
self.successfactor_username = env.successfactor_username
self.successfactor_password = env.successfactor_password
self.successfactor_host = env.successfactor_host
self.psdatabase = Utility()
def __init__(self):
self.env = env
self.psdatabase = Utility()
class EmployeeDetails():
def __init__(self):
self.env = env
self.dbuser = env.postgreSql_dbuser
self.dbpswd = env.postgreSql_dbpswd
self.dbhost = env.postgreSql_dbhost
self.dbport = env.postgreSql_dbport
self.dbname = env.postgreSql_dbname
self.tableconversation = env.postgreSql_table_conversation
self.successfactor_username = env.successfactor_username
self.successfactor_password = env.successfactor_password
self.successfactor_host = env.successfactor_host
self.psdatabase = Utility()
def insertintoColleagueDb(self, colleague_json_file_path):
employee_colleague_details = self.getDataFromJsonFile(
colleague_json_file_path)
self.create_colleague_table()
self.insert_colleague_data(employee_colleague_details)
return "Colleagues data inserted in ColleagueDetails table"
def createColleagueDataForDbinsert(self):
colleagueList = self.getAllColleagueDetails(self.EmployeeData())
my_path = Path(__file__).parent.parent
os.path.abspath(os.path.dirname(__file__))
colleague_json_file_path = str(
my_path) + "\\" + "jsonFiles" + "\\" + 'Demo_Colleague'
# self.writeIntoJsonFile('Demo_Colleague.json',colleagueList)
if pathlib.Path(colleague_json_file_path + '.json'):
os.remove(colleague_json_file_path + '.json')
self.writeIntoJsonFile(colleague_json_file_path, colleagueList)
return "colleague data created successfully"
## Create Table
def create_tables(self):
""" create tables in the PostgreSQL database"""
commands = ("""
CREATE TABLE IF NOT EXISTS Demo_Employee (
userId VARCHAR(255) PRIMARY KEY,
username VARCHAR(255) ,
division VARCHAR(255) ,
defaultFullName VARCHAR(255),
country VARCHAR(255) ,
firstName VARCHAR(255),
lastName VARCHAR(255) ,
jobCode VARCHAR(255) ,
location VARCHAR(255) ,
department VARCHAR(255),
title VARCHAR(255) ,
businessPhone VARCHAR(255) ,
email VARCHAR(255) ,
cellPhone VARCHAR(255),
managerId VARCHAR(255),
hrId VARCHAR(255)
) """, """ CREATE TABLE IF NOT EXISTS Demo_Manager (
managerId VARCHAR(255) PRIMARY KEY,
division VARCHAR(255) ,
defaultFullName VARCHAR(255),
country VARCHAR(255) ,
firstName VARCHAR(255),
lastName VARCHAR(255) ,
jobCode VARCHAR(255) ,
location VARCHAR(255) ,
department VARCHAR(255),
email VARCHAR(255)
) """, """ CREATE TABLE IF NOT EXISTS Demo_HR (
hrId VARCHAR(255) PRIMARY KEY,
division VARCHAR(255) ,
defaultFullName VARCHAR(255),
country VARCHAR(255) ,
firstName VARCHAR(255),
lastName VARCHAR(255) ,
jobCode VARCHAR(255) ,
location VARCHAR(255) ,
department VARCHAR(255),
email VARCHAR(255)
) """)
for command in commands:
print(command)
self.psdatabase.connect_execute(env.create_query_type, command)
print("Demo_Employee , Demo_Manager and Demo_HR tables are created")
def insertEmployeeData(self, allEmployeeData):
try:
connection = psycopg2.connect(user=self.dbuser,
password=self.dbpswd,
host=self.dbhost,
port=self.dbport,
database=self.dbname)
cursor = connection.cursor()
for employee in allEmployeeData:
postgres_employee_insert_query = """ INSERT INTO Demo_Employee (userId, username, division,defaultFullName,country,firstName,lastName,jobCode,location,department,title,businessPhone,email,cellPhone,managerId,hrId) VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"""
if employee['hr'] is not None:
hrId = employee['hr']['userId']
else:
hrId = None
if employee['manager'] is not None:
managerId = employee['manager']['userId']
else:
managerId = None
cursor.execute(
"""select exists(select 1 from Demo_Employee where userId = %s)""",
(employee['userId'], ))
row = cursor.fetchone()
# need to check , getting duplicate user id 100009
# if row[0] == False and employee['userId'] != '100009':
if row[0] == False:
employee_record_to_insert = (
employee['userId'], employee['username'],
employee['division'], employee['defaultFullName'],
employee['country'], employee['firstName'],
employee['lastName'], employee['jobCode'],
employee['location'], employee['department'],
employee['title'], employee['businessPhone'],
employee['email'], employee['cellPhone'], managerId,
hrId)
cursor.execute(postgres_employee_insert_query,
employee_record_to_insert)
# First checking if the record already exists
cursor.execute(
"""select exists(select 1 from Demo_Manager where managerId = %s)""",
(managerId, ))
row = cursor.fetchone()
if row[0] == False and managerId is not None:
postgres_manager_insert_query = """ INSERT INTO Demo_Manager (managerId, division,defaultFullName,country,firstName,lastName,jobCode,location,department,email) VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s) """
manager_record_to_insert = (
managerId, employee['division'],
employee['manager']['defaultFullName'],
employee['manager']['country'],
employee['manager']['firstName'],
employee['manager']['lastName'],
employee['manager']['jobCode'],
employee['manager']['location'],
employee['manager']['department'], None)
cursor.execute(postgres_manager_insert_query,
manager_record_to_insert)
# First checking if the record already exists
cursor.execute(
"""select exists(select 1 from Demo_HR where hrId = %s)""",
(hrId, ))
row = cursor.fetchone()
if row[0] == False and hrId is not None:
postgres_hr_insert_query = """ INSERT INTO Demo_HR (hrId, division,defaultFullName,country,firstName,lastName,jobCode,location,department,email) VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"""
hr_record_to_insert = (hrId, employee['hr']['division'],
employee['hr']['defaultFullName'],
employee['hr']['country'],
employee['hr']['firstName'],
employee['hr']['lastName'],
employee['hr']['jobCode'],
employee['hr']['location'],
employee['hr']['department'],
employee['hr']['email'])
cursor.execute(postgres_hr_insert_query,
hr_record_to_insert)
# # commit the changes
connection.commit()
except (Exception, psycopg2.Error) as error:
print("Error while connecting to PostgreSQL", error)
finally:
# closing database connection.
if (connection):
cursor.close()
connection.close()
return "records inserted"
def create_colleague_table(self):
""" create tables in the PostgreSQL database"""
command = """CREATE TABLE IF NOT EXISTS Demo_ColleagueDetails (
userId VARCHAR(255) PRIMARY KEY,
colleagues TEXT []
) """
self.psdatabase.connect_execute(env.create_query_type, command)
print("Demo_ColleagueDetails table is created")
def insert_colleague_data(self, ComleteEmloyeeDetails_Test):
try:
connection = psycopg2.connect(user=self.dbuser,
password=self.dbpswd,
host=self.dbhost,
port=self.dbport,
database=self.dbname)
cursor = connection.cursor()
postgres_colleague_insert_query = """ INSERT INTO Demo_ColleagueDetails (userId, colleagues) VALUES (%s,%s) """
for colleague in ComleteEmloyeeDetails_Test:
cursor.execute(
"""select exists(select 1 from Demo_ColleagueDetails where userId = %s)""",
(colleague['userId'], ))
row = cursor.fetchone()
if row[0] == False:
manager_record_to_insert = (colleague['userId'],
colleague['colleague_List'])
cursor.execute(postgres_colleague_insert_query,
manager_record_to_insert)
connection.commit()
except (Exception, psycopg2.Error) as error:
print("Error while connecting to PostgreSQL", error)
finally:
# closing database connection.
if (connection):
cursor.close()
connection.close()
def getEmployeeDetailsInBatch(self, top, skip):
employee_Details_url = self.successfactor_host + "/User?$format=json&$expand=manager,hr&$select=userId,email,jobCode,location,country,title,username,division,defaultFullName,department,firstName,lastName,businessPhone,cellPhone,manager/userId,hr/email,manager/country,manager/firstName,manager/lastName,manager/location,manager/jobCode,manager/division,manager/defaultFullName,manager/department,hr/userId,hr/email,hr/country,hr/firstName,hr/lastName,hr/location,hr/jobCode,hr/division,hr/defaultFullName,hr/department&$top={0}&$skip={1}".format(
top, skip)
employee_Details_res = requests.get(url=employee_Details_url,
auth=HTTPBasicAuth(
self.successfactor_username,
self.successfactor_password))
# print("batch education done for employees :"+skip)
print(employee_Details_url)
print(employee_Details_res)
employee_Details_json_data = employee_Details_res.json()
employee_Details_results = employee_Details_json_data['d']['results']
# print(employee_time_results)
return employee_Details_results
def getEmployeeCount(self):
Employee_count_url = self.successfactor_host + "/User/$count?$format=json"
picture_count = requests.get(url=Employee_count_url,
auth=HTTPBasicAuth(
self.successfactor_username,
self.successfactor_password))
return picture_count.json()
def getAllEmployeeDetails(self, batchSize):
#To get Employee Details in Batch
All_Employee_Details = []
count = 0
employeeCount = self.getEmployeeCount()
for i in range(1, employeeCount):
if i % batchSize == 0 or i == 0:
count = count + 1
top = batchSize
employees = self.getEmployeeDetailsInBatch(top, i)
All_Employee_Details.extend(employees)
if i == (employeeCount - 1):
top = i - count * batchSize
employees = self.getEmployeeDetailsInBatch(top, i)
All_Employee_Details.extend(employees)
return All_Employee_Details
def getColleagueList(self, userId):
if self.getManagerId(userId):
colleague_list_url = self.successfactor_host + "/User?$format=json&$filter=userId eq '{0}'&$expand=directReports&$select=userId,directReports/userId,directReports/defaultFullName".format(
self.getManagerId(userId))
res = requests.get(url=colleague_list_url,
auth=HTTPBasicAuth(self.successfactor_username,
self.successfactor_password))
json_data = res.json()
results = json_data['d']['results']
# print(results)
colleague_List = []
data = {}
for r in results:
for result in r['directReports']['results']:
# print(result['userId'])
colleague_List.append(result['userId'])
# data['colleagueList']=colleague_List
# print("userId "+str(userId)+"colleague_List"+str(colleague_List))
if userId in colleague_List:
colleague_List.remove(userId)
data['userId'] = userId
data['colleague_List'] = colleague_List
return data
else:
return {"userId": userId, "colleague_List": []}
def getManagerId(self, userId):
employee_time_url = self.successfactor_host + "/User?$format=json&$filter=userId eq '{0}'&$expand=manager&$select=userId,manager/userId".format(
userId)
employee_time_res = requests.get(url=employee_time_url,
auth=HTTPBasicAuth(
self.successfactor_username,
self.successfactor_password))
employee_time_json_data = employee_time_res.json()
employee_time_results = employee_time_json_data['d']['results']
managerId = ''
if employee_time_results is not None:
for manager in employee_time_results:
# print(manager['manager']['userId'])
try:
managerId = manager['manager']['userId']
except TypeError:
print("Manager Id is not found for: " + userId)
return managerId
else:
print("Manager Id is not found for: " + userId)
return managerId
def getAllColleagueDetails(self, allEmployeeData):
colleagueList = []
count = 0
for employee in range(len(allEmployeeData)):
employee = self.getColleagueList(
allEmployeeData[employee]['userId'])
colleagueList.append(employee)
count = count + 1
if count % 100 == 0:
print(
"Completed finding colleague Details for {0} number of employees"
.format(count))
return colleagueList
def EmployeeData(self):
postgres_employee_select_query = """select
json_build_object(
'userId', emp.userId,
'username', emp.username,
'division', emp.division,
'defaultFullName', emp.defaultFullName,
'country', emp.country,
'firstName', emp.firstName,
'lastName', emp.lastName,
'jobCode', emp.jobCode,
'location', emp.location,
'department', emp.department,
'title', emp.title,
'businessPhone', emp.businessPhone,
'email', emp.email,
'cellPhone', emp.cellPhone,
'manager', json_build_object(
'userId', man.managerId,
'division', man.division,
'defaultFullName', man.defaultFullName,
'country', man.country,
'lastName', man.lastName,
'firstName', man.firstName,
'jobCode', man.jobCode,
'location', man.location,
'department', man.department
),
'hr', json_build_object(
'userId', hr.hrId,
'division', hr.division,
'defaultFullName', hr.defaultFullName,
'country', hr.country,
'lastName', hr.lastName,
'firstName', hr.firstName,
'jobCode', hr.jobCode,
'location', hr.location,
'department', hr.department,
'email', hr.email
)
)
from Demo_Employee emp
INNER JOIN Demo_Manager man ON emp.managerId = man.managerId
INNER JOIN Demo_HR hr ON emp.hrId = hr.hrId """
employee_records_json = []
employee_records = self.psdatabase.connect_execute(
env.select_query_type, postgres_employee_select_query)
for employee in employee_records:
employee_records_json.append(employee[0])
return employee_records_json
def employeeIDs(self, employeeCount):
employee_detils_list = self.EmployeeData()
empoyeeIdList = []
# taking top 10 values as of now
for employee in employee_detils_list[0:employeeCount]:
for element in employee:
if element == 'userId':
empoyeeIdList.append(employee.get(element))
# print(empDetails)
for emp in empoyeeIdList[:]:
if len(emp) == 0:
empoyeeIdList.remove(emp)
return empoyeeIdList
def getColleagueDetailsWithOutImage(self, colleagues):
detailedList = []
try:
connection = psycopg2.connect(user=self.dbuser,
password=self.dbpswd,
host=self.dbhost,
port=self.dbport,
database=self.dbname)
cursor = connection.cursor()
for emp in colleagues:
dictionary = {}
postgres_employee_select_query = '''select
json_build_object(
'userId', emp.userId,
'username', emp.username,
'division', emp.division,
'defaultFullName', emp.defaultFullName,
'country', emp.country,
'firstName', emp.firstName,
'lastName', emp.lastName,
'jobCode', emp.jobCode,
'location', emp.location,
'department', emp.department,
'title', emp.title,
'businessPhone', emp.businessPhone,
'email', emp.email,
'cellPhone', emp.cellPhone,
'manager', json_build_object(
'userId', man.managerId,
'division', man.division,
'defaultFullName', man.defaultFullName,
'country', man.country,
'lastName', man.lastName,
'firstName', man.firstName,
'jobCode', man.jobCode,
'location', man.location,
'department', man.department
),
'hr', json_build_object(
'userId', hr.hrId,
'division', hr.division,
'defaultFullName', hr.defaultFullName,
'country', hr.country,
'lastName', hr.lastName,
'firstName', hr.firstName,
'jobCode', hr.jobCode,
'location', hr.location,
'department', hr.department,
'email', hr.email
)
)
from Demo_Employee emp
INNER JOIN Demo_Manager man ON emp.managerId = man.managerId
INNER JOIN Demo_HR hr ON emp.hrId = hr.hrId
WHERE userId = %s '''
cursor.execute(postgres_employee_select_query, (emp, ))
emp_data = cursor.fetchall()
if len(emp_data) > 0:
detailedList.append(emp_data[0][0])
except (Exception, psycopg2.Error) as error:
print("Error while connecting to PostgreSQL", error)
finally:
# closing database connection.
if (connection):
cursor.close()
connection.close()
return detailedList
def getEmployeeDetailsWithOutImage(self, emp):
dictionary = {}
try:
connection = psycopg2.connect(user=self.dbuser,
password=self.dbpswd,
host=self.dbhost,
port=self.dbport,
database=self.dbname)
cursor = connection.cursor()
postgres_employee_select_query = '''select
json_build_object(
'userId', emp.userId,
'username', emp.username,
'division', emp.division,
'defaultFullName', emp.defaultFullName,
'country', emp.country,
'firstName', emp.firstName,
'lastName', emp.lastName,
'jobCode', emp.jobCode,
'location', emp.location,
'department', emp.department,
'title', emp.title,
'businessPhone', emp.businessPhone,
'email', emp.email,
'cellPhone', emp.cellPhone,
'manager', json_build_object(
'userId', man.managerId,
'division', man.division,
'defaultFullName', man.defaultFullName,
'country', man.country,
'lastName', man.lastName,
'firstName', man.firstName,
'jobCode', man.jobCode,
'location', man.location,
'department', man.department
),
'hr', json_build_object(
'userId', hr.hrId,
'division', hr.division,
'defaultFullName', hr.defaultFullName,
'country', hr.country,
'lastName', hr.lastName,
'firstName', hr.firstName,
'jobCode', hr.jobCode,
'location', hr.location,
'department', hr.department,
'email', hr.email
)
)
from Demo_Employee emp
INNER JOIN Demo_Manager man ON emp.managerId = man.managerId
INNER JOIN Demo_HR hr ON emp.hrId = hr.hrId
WHERE userId = %s '''
cursor.execute(postgres_employee_select_query, (emp, ))
emp_data = cursor.fetchall()
dictionary.update(emp_data[0][0])
while ("" in dictionary):
dictionary.remove("")
except (Exception, psycopg2.Error) as error:
print("Error while connecting to PostgreSQL", error)
finally:
# closing database connection.
if (connection):
cursor.close()
connection.close()
return dictionary
def insertCompleteEmployeeData(self, empList):
finalList = []
count = 0
for emp in empList:
try:
empDictionary = self.getEmployeeDetailsWithOutImage(emp)
obj = self.getColleagueList(emp)
colleagues = obj['colleague_List']
colleagueList = self.getColleagueDetailsWithOutImage(
colleagues)
for col in colleagueList[:]:
if len(col) == 0:
colleagueList.remove(col)
empDictionary["colleagueDetailsList"] = colleagueList
count = count + 1
self.insert_complete_data(empDictionary)
if count % 100 == 0:
print(
"Completed finding Employee Details for {0} number of employees"
.format(count))
except Exception as ex:
print(ex)
return "Employee complete information get Created and stored in the DataBase"
def createCompleteEmployeeInfoTable(self):
command = """CREATE TABLE IF NOT EXISTS demo_employee_details (
userId VARCHAR(255) PRIMARY KEY,
fullName VARCHAR(255) ,
location VARCHAR(255),
completeInfo json
) """
self.psdatabase.connect_execute(env.create_query_type, command)
print("demo_employee_details table created")
def insert_complete_data(self, emp):
try:
connection = psycopg2.connect(user=self.dbuser,
password=self.dbpswd,
host=self.dbhost,
port=self.dbport,
database=self.dbname)
cursor = connection.cursor()
postgres_colleague_insert_query = """ INSERT INTO demo_employee_details (userId,fullName,location, completeInfo) VALUES (%s,%s,%s,%s) """
cursor.execute(
"""select exists(select 1 from demo_employee_details where userId = %s)""",
(emp['userId'], ))
row = cursor.fetchone()
if row[0] == False:
manager_record_to_insert = (emp['userId'],
emp['defaultFullName'],
emp['location'], json.dumps(emp))
cursor.execute(postgres_colleague_insert_query,
manager_record_to_insert)
connection.commit()
except (Exception, psycopg2.Error) as error:
print("Error while connecting to PostgreSQL", error)
finally:
# closing database connection.
if (connection):
cursor.close()
connection.close()
def getEmpInfoJsonFile(self, limit):
employee_details_list = []
postgres_employee_select_query = "select completeinfo from demo_employee_details limit " + str(
limit)
emp_data = self.psdatabase.connect_execute(
env.select_query_type, postgres_employee_select_query)
for emp in emp_data:
employee_details_list.append(emp[0])
return employee_details_list
import json
from reports.postgreDashboard import DashBoard
from config.env import env
from postgreSql.connection import Utility
from postgreSql.postgresqlQuery import EmployeeDetails
psdatabase = Utility()
_dashBoard = DashBoard()
_employeeDetails = EmployeeDetails()
class analyticsappi():
def __init__(self):
self.env = env
self.psdatabase = Utility()
def getSupportAndLiveChatDetails(self):
previousId = """select table_id from dashboard_api_logDetails where type='Support and Live Chat Details'"""
id = ("".join(
json.dumps(
psdatabase.connect_execute(
query_type="select",
dbQuery=previousId)))).strip("[]").strip('"')
if len(id) == 0:
support_chat_data = """select id ,intent_name,data from conversation_event
where intent_name is not null and intent_name!='feedback'"""
else:
class analyticsappi():
def __init__(self):
self.env = env
self.psdatabase = Utility()
def getSupportAndLiveChatDetails(self):
previousId = """select table_id from dashboard_api_logDetails where type='Support and Live Chat Details'"""
id = ("".join(
json.dumps(
psdatabase.connect_execute(
query_type="select",
dbQuery=previousId)))).strip("[]").strip('"')
if len(id) == 0:
support_chat_data = """select id ,intent_name,data from conversation_event
where intent_name is not null and intent_name!='feedback'"""
else:
support_chat_data = str(
"""select id ,intent_name,data from conversation_event
where intent_name is not null and intent_name!='feedback' and id={0}"""
).format(id)
support_chat_data_event_data = self.psdatabase.connect_execute(
query_type="select", dbQuery=support_chat_data)
_dashBoard.prepareSupportAndChatData(support_chat_data_event_data)
support_chat_data = str(
"""select sender_id,event,timestamp,text,table_id,user_intent,confidence,environment,main_intent from getSupportAndLiveChatDetails"""
)
support_chat_data_event_data = psdatabase.connect_execute(
query_type="select", dbQuery=support_chat_data)
support_action_list = []
for row in support_chat_data_event_data:
final_dict = {}
final_dict['sender_id'] = row[0]
final_dict['event'] = row[1]
final_dict['timestamp'] = row[2]
final_dict['text'] = row[3]
final_dict['table_id'] = row[4]
final_dict['user_intent'] = row[5]
final_dict['confidence'] = row[6]
final_dict['environment'] = row[7]
final_dict['main_intent'] = row[8]
support_action_list.append(final_dict)
return support_action_list
def getLikeandDislikeDetails(self):
previousId = """select table_id from dashboard_api_logDetails where type='Like and Dislike'"""
id = ("".join(
json.dumps(
psdatabase.connect_execute(
query_type="select",
dbQuery=previousId)))).strip("[]").strip('"')
if len(id) == 0:
like_and_dislike = """select id ,type_name,intent_name,action_name,data from conversation_event"""
else:
like_and_dislike = str(
"""select id ,type_name,intent_name,action_name,data from conversation_event where id={0}"""
).format(id)
like_and_dislike_data = self.psdatabase.connect_execute(
query_type="select", dbQuery=like_and_dislike)
_dashBoard.preparelikeAndDislike(like_and_dislike_data)
conversation_event_data = str(
"""select sender_id,timestamp,table_id,like_dislike,environment,main_intent from getLikeDislikeDetails"""
)
like_and_dislike = psdatabase.connect_execute(
query_type="select", dbQuery=conversation_event_data)
like_and_dislike_list = []
for row in like_and_dislike:
final_dict = {}
final_dict['sender_id'] = row[0]
final_dict['timestamp'] = row[1]
final_dict['table_id'] = row[2]
final_dict['like_dislike'] = row[3]
final_dict['environment'] = row[4]
final_dict['main_event'] = row[5]
like_and_dislike_list.append(final_dict)
return like_and_dislike_list
def getFallbackData(self):
previousId = """select table_id from dashboard_api_logDetails where type='Fallback Details'"""
id = ("".join(
json.dumps(
psdatabase.connect_execute(
query_type="select",
dbQuery=previousId)))).strip("[]").strip('"')
if len(id) == 0:
select_conversation_event = "select id,data from conversation_event"
else:
select_conversation_event = str(
"""select id,data from conversation_event where id={0}"""
).format(id)
conversation_event_data = self.psdatabase.connect_execute(
query_type="select", dbQuery=select_conversation_event)
_dashBoard.prepareFallBackData(conversation_event_data)
select_conversation_event = str(
"""select sender_id,timestamp,text,intent,confidence,environment,action,table_id from getfallbackDetails"""
)
conversation_event_data = psdatabase.connect_execute(
query_type="select", dbQuery=select_conversation_event)
fallback_action_list = []
for row in conversation_event_data:
final_dict = {}
final_dict['sender_id'] = row[0]
final_dict['timestamp'] = row[1]
final_dict['text'] = row[2]
final_dict['intent'] = row[3]
final_dict['confidence'] = row[4]
final_dict['environment'] = row[5]
final_dict['action'] = row[6]
final_dict['table_id'] = row[7]
fallback_action_list.append(final_dict)
return fallback_action_list
def postgreAnalyticsuserMessage(self):
previousId = """select table_id from dashboard_api_logDetails where type='Analytics Data'"""
id = ("".join(
json.dumps(
psdatabase.connect_execute(
query_type="select",
dbQuery=previousId)))).strip("[]").strip('"')
if len(id) == 0:
select_conversation_event = "select id,data from conversation_event"
else:
select_conversation_event = str(
"""select id,data from conversation_event where id={0}"""
).format(id)
conversation_event_data = self.psdatabase.connect_execute(
query_type="select", dbQuery=select_conversation_event)
select_conversation = "select sender_id ,latest_event_time from conversation "
conversation_data = self.psdatabase.connect_execute(
query_type="select", dbQuery=select_conversation)
_dashBoard.prepareData(conversation_data, conversation_event_data)
select_conversation_event = str(
"""select sender_id,event,timestamp,text,table_id,name,confidence,environment,latest_event_time from getmessages"""
)
conversation_event_data = psdatabase.connect_execute(
query_type="select", dbQuery=select_conversation_event)
final_list = []
for row in conversation_event_data:
final_dict = {}
final_dict['sender_id'] = row[0]
final_dict['event'] = row[1]
final_dict['timestamp'] = row[2]
final_dict['text'] = row[3]
final_dict['table_id'] = row[4]
final_dict['name'] = row[5]
final_dict['confidence'] = row[6]
final_dict['environment'] = row[7]
final_dict['last_event_time'] = row[8]
final_list.append(final_dict)
return final_list
def getEmpInfo(self):
employee_details_list = []
postgres_employee_select_query = "select completeinfo from o360_employee_details"
emp_data = psdatabase.connect_execute(
query_type="select", dbQuery=postgres_employee_select_query)
for emp in emp_data:
employee_details_list.append(emp[0])
print(employee_details_list)
return employee_details_list
def insertEmployeeDB(self):
allEmployeeData = _employeeDetails.getAllEmployeeDetails(env.batchSize)
_employeeDetails.create_tables()
_employeeDetails.insertEmployeeData(allEmployeeData)
return "Employee data inserted in Employee , HR and Manager tables"
def getEmployeeData(self):
message = _employeeDetails.EmployeeData()
return message
def createAndinsertEmployeeCompleteData(self):
_employeeDetails.createCompleteEmployeeInfoTable()
message = _employeeDetails.insertCompleteEmployeeData(
_employeeDetails.employeeIDs(_employeeDetails.getEmployeeCount()))
return message
def getCompleteEmployeeData(self):
all_employee_data_from_file = _employeeDetails.getEmpInfoJsonFile(
_employeeDetails.getEmployeeCount())
return all_employee_data_from_file
from sanic import response
import json
from config.env import env
from postgreSql.connection import Utility
from mail.sendMail import generateEmail
_connectionDB = Utility()
email = generateEmail()
def triggerMail(function):
def inner_function(request):
if request.method == 'POST':
dbCountQuery = "SELECT count(*) FROM o360_employee WHERE empid ~* '^[A-Z]'"
present_count = int(("".join(
json.dumps(
_connectionDB.connect_execute(env.select_query_type,
dbCountQuery)))).strip("[]"))
dbCountQuery = "select license_count from param"
max_count = int(("".join(
json.dumps(
_connectionDB.connect_execute(env.select_query_type,
dbCountQuery)))).strip("[]"))
if present_count > max_count:
# email.SendMail()
return response.json({"message": "User limit exceeded"})
else:
return function(request)
else:
return function(request)
class analyticsappi(dashboardapi):
def __init__(self):
self.psdatabase = Utility()
def postgreAnalyticsuserMessage(self):
previousId = """select table_id from dashboard_api_logDetails where type='Analytics Data'"""
id = ("".join(
json.dumps(
self.psdatabase.connect_execute(
env.select_query_type,
previousId)))).strip("[]").strip('"')
if len(id) == 0:
select_conversation_event = "select id,data from conversation_event"
else:
select_conversation_event = str(
"""select id,data from conversation_event where id>{0}"""
).format(id)
conversation_event_data = self.psdatabase.connect_execute(
env.select_query_type, select_conversation_event)
select_conversation = "select sender_id ,latest_event_time from conversation "
conversation_data = self.psdatabase.connect_execute(
env.select_query_type, select_conversation)
_dashBoard.prepareData(conversation_data, conversation_event_data)
select_conversation_event = str(
"""select sender_id,event,timestamp,text,table_id,name,confidence,environment,latest_event_time from getmessages"""
)
conversation_event_data = self.psdatabase.connect_execute(
env.select_query_type, select_conversation_event)
final_list = []
for row in conversation_event_data:
final_dict = {}
final_dict['sender_id'] = row[0]
final_dict['event'] = row[1]
final_dict['timestamp'] = row[2]
final_dict['text'] = row[3]
final_dict['table_id'] = row[4]
final_dict['name'] = row[5]
final_dict['confidence'] = row[6]
final_dict['environment'] = row[7]
final_dict['last_event_time'] = row[8]
final_list.append(final_dict)
return final_list
def getFallbackData(self):
previousId = """select table_id from dashboard_api_logDetails where type='Fallback Details'"""
id = ("".join(
json.dumps(
self.psdatabase.connect_execute(
env.select_query_type,
previousId)))).strip("[]").strip('"')
if len(id) == 0:
select_conversation_event = "select id,data from conversation_event"
else:
select_conversation_event = str(
"""select id,data from conversation_event where id>{0}"""
).format(id)
conversation_event_data = self.psdatabase.connect_execute(
env.select_query_type, select_conversation_event)
_dashBoard.prepareFallBackData(conversation_event_data)
select_conversation_event = str(
"""select sender_id,timestamp,text,intent,confidence,environment,action,table_id from getfallbackDetails"""
)
conversation_event_data = self.psdatabase.connect_execute(
env.select_query_type, select_conversation_event)
fallback_action_list = []
for row in conversation_event_data:
final_dict = {}
final_dict['sender_id'] = row[0]
final_dict['timestamp'] = row[1]
final_dict['text'] = row[2]
final_dict['intent'] = row[3]
final_dict['confidence'] = row[4]
final_dict['environment'] = row[5]
final_dict['action'] = row[6]
final_dict['table_id'] = row[7]
fallback_action_list.append(final_dict)
return fallback_action_list
def getSupportAndLiveChatDetails(self):
previousId = """select table_id from dashboard_api_logDetails where type='Support and Live Chat Details'"""
id = ("".join(
json.dumps(
self.psdatabase.connect_execute(
env.select_query_type,
previousId)))).strip("[]").strip('"')
if len(id) == 0:
support_chat_data = """select id ,intent_name,data from conversation_event
where intent_name is not null and intent_name!='feedback'"""
else:
support_chat_data = str(
"""select id ,intent_name,data from conversation_event
where intent_name is not null and intent_name!='feedback' and id>{0}"""
).format(id)
support_chat_data_event_data = self.psdatabase.connect_execute(
env.select_query_type, support_chat_data)
_dashBoard.prepareSupportAndChatData(support_chat_data_event_data)
support_chat_data = str(
"""select sender_id,event,timestamp,text,table_id,user_intent,confidence,environment,main_intent from getSupportAndLiveChatDetails"""
)
support_chat_data_event_data = self.psdatabase.connect_execute(
env.select_query_type, support_chat_data)
support_action_list = []
for row in support_chat_data_event_data:
final_dict = {}
final_dict['sender_id'] = row[0]
final_dict['event'] = row[1]
final_dict['timestamp'] = row[2]
final_dict['text'] = row[3]
final_dict['table_id'] = row[4]
final_dict['user_intent'] = row[5]
final_dict['confidence'] = row[6]
final_dict['environment'] = row[7]
final_dict['main_intent'] = row[8]
support_action_list.append(final_dict)
return support_action_list
def getLikeandDislikeDetails(self):
previousId = """select table_id from dashboard_api_logDetails where type='Like and Dislike'"""
id = ("".join(
json.dumps(
self.psdatabase.connect_execute(
env.select_query_type,
previousId)))).strip("[]").strip('"')
if len(id) == 0:
like_and_dislike = """select id ,type_name,intent_name,action_name,data from conversation_event"""
else:
like_and_dislike = str(
"""select id ,type_name,intent_name,action_name,data from conversation_event where id>{0}"""
).format(id)
like_and_dislike_data = self.psdatabase.connect_execute(
env.select_query_type, like_and_dislike)
_dashBoard.preparelikeAndDislike(like_and_dislike_data)
conversation_event_data = str(
"""select sender_id,timestamp,table_id,like_dislike,environment,main_intent from getLikeDislikeDetails"""
)
like_and_dislike = self.psdatabase.connect_execute(
env.select_query_type, conversation_event_data)
like_and_dislike_list = []
for row in like_and_dislike:
final_dict = {}
final_dict['sender_id'] = row[0]
final_dict['timestamp'] = row[1]
final_dict['table_id'] = row[2]
final_dict['like_dislike'] = row[3]
final_dict['environment'] = row[4]
final_dict['main_event'] = row[5]
like_and_dislike_list.append(final_dict)
return like_and_dislike_list
def getEmpInfo(self):
employee_details_list = []
postgres_employee_select_query = "select completeinfo from employee_details"
emp_data = self.psdatabase.connect_execute(
env.select_query_type, postgres_employee_select_query)
for emp in emp_data:
employee_details_list.append(emp[0])
return employee_details_list
def insertEmployeeDB(self):
allEmployeeData = _employeeDetails.getAllEmployeeDetails(env.batchSize)
_employeeDetails.create_tables()
_employeeDetails.insertEmployeeData(allEmployeeData)
return "Employee data inserted in Employee , HR and Manager tables"
def getEmployeeData(self):
message = _employeeDetails.EmployeeData()
return message
def createAndinsertEmployeeCompleteData(self):
_employeeDetails.createCompleteEmployeeInfoTable()
message = _employeeDetails.insertCompleteEmployeeData(
_employeeDetails.employeeIDs(_employeeDetails.getEmployeeCount()))
return message
def getCompleteEmployeeData(self):
all_employee_data_from_file = _employeeDetails.getEmpInfoJsonFile(
_employeeDetails.getEmployeeCount())
return all_employee_data_from_file
def mongoAnalyticsusermessages(self):
chatmessages = self.dashboardusermessages()
chatmessages = self.build_new_user(chatmessages)
flattenData = jsonconvert.flatten_nested_json(list(chatmessages))
return flattenData
# new user calculating
def build_new_user(self, messages):
today = datetime.datetime.now()
today = today.replace(hour=0, minute=0, second=0, microsecond=0)
for message in messages:
events = message["events"]
newEvents = []
isNewUser = None
for event in events:
isNewUser = datetime.datetime.fromtimestamp(
event["timestamp"]
) > today if isNewUser == None else isNewUser
event['isNewUser'] = isNewUser
# if event['text'] not in ['/weather', '/home', '/restart']:
# newEvents.append(event)
# message["events"] = newEvents
return messages
def analyticsemployeedet(self):
return self.dashboardemployeedet()
def analyticsconversation(self):
return self.dashboardconversation()
class DashBoard():
def __init__(self):
self.env = env
self.psdatabase = Utility()
def prepareFallBackData(self, conversation_event_data):
fallback_action_list = []
i = 1
for event_data in conversation_event_data:
try:
table_id = event_data[0]
inner_dictionary = json.loads(event_data[1])
if "event" in inner_dictionary:
if inner_dictionary["event"] == 'user':
wanted_keys = ("sender_id", "name", "event",
"latest_event_time", "text",
"timestamp")
dictfilt = lambda x, y: dict([(i, x[i]) for i in x
if i in set(y)])
user_dict = dictfilt(inner_dictionary, wanted_keys)
user_dict['table_id'] = table_id
if "parse_data" in inner_dictionary and "metadata" in inner_dictionary:
user_dict['intent'] = inner_dictionary[
'parse_data']['intent']['name']
user_dict['confidence'] = inner_dictionary[
'parse_data']['intent']['confidence']
if 'environment' in inner_dictionary["metadata"]:
user_dict['environment'] = inner_dictionary[
"metadata"]['environment']
else:
user_dict['environment'] = "others"
if inner_dictionary["event"] == 'action':
wanted_keys = ("sender_id", "name",
"latest_event_time", "text",
"timestamp")
dictfilt = lambda x, y: dict([(i, x[i]) for i in x
if i in set(y)])
final_dict = dictfilt(inner_dictionary, wanted_keys)
action_table_id = table_id
if final_dict["name"] == 'my_fallback_action':
if final_dict['sender_id'] == user_dict[
'sender_id'] and action_table_id == int(
user_dict['table_id']) + 1:
final_dict['text'] = user_dict['text']
final_dict['intent'] = user_dict['intent']
final_dict['confidence'] = user_dict[
'confidence']
if "environment" in user_dict:
final_dict['environment'] = user_dict[
'environment']
final_dict['action'] = final_dict.pop('name')
# to check table id , helpful for debugging
final_dict['table_id'] = table_id
user_dict = {}
fallback_action_list.append(final_dict)
if i == len(conversation_event_data):
data = json.loads(event_data[1])
if "timestamp" in data and "sender_id" in data:
delete_query = "DELETE FROM dashboard_api_logDetails where type='Fallback Details'"
self.psdatabase.connect_execute(env.delete_query_type,
dbQuery=delete_query)
insert_fallback_details = """INSERT INTO dashboard_api_logDetails ( sender_id, timestamp,type,table_id)values(%s,%s,%s,%s)"""
data = (data["sender_id"], data["timestamp"],
"Fallback Details", event_data[0])
self.psdatabase.connect_execute(
env.insert_query_type,
dbQuery=insert_fallback_details,
params=data)
i = i + 1
except Exception as e:
print("Exception occured !!!")
print(e)
try:
insert_fallback_details = """INSERT INTO getfallbackDetails ( sender_id, timestamp, text, intent, confidence, environment,action,table_id)
values(%s,%s,%s,%s,%s,%s,%s,%s)"""
for list in fallback_action_list:
fallback_details = []
for val in list.values():
fallback_details.append(val)
self.psdatabase.connect_execute(
env.insert_query_type,
dbQuery=insert_fallback_details,
params=fallback_details)
print(fallback_details)
except Exception as e:
print("Exception occured")
print(e)
return fallback_action_list
def prepareSupportAndChatData(self, support_chat_data_event_data):
support_action_list = []
test_dict = {}
get_main_intent = False
i = 1
for event_data in support_chat_data_event_data:
table_id = event_data[0]
intent_name = event_data[1]
inner_dictionary = json.loads(event_data[2])
if intent_name == 'email_support' or intent_name == 'live_agent':
get_main_intent = True
if "event" in inner_dictionary:
if inner_dictionary["event"] == 'user':
wanted_keys = ("sender_id", "name", "event",
"latest_event_time", "text",
"timestamp")
dictfilt = lambda x, y: dict([(i, x[i]) for i in x
if i in set(y)])
test_dict = dictfilt(inner_dictionary, wanted_keys)
test_dict['table_id'] = table_id
if "parse_data" in inner_dictionary and "metadata" in inner_dictionary:
test_dict['user_intent'] = inner_dictionary[
'parse_data']['intent']['name']
test_dict['confidence'] = inner_dictionary[
'parse_data']['intent']['confidence']
if 'environment' in inner_dictionary["metadata"]:
test_dict['environment'] = inner_dictionary[
"metadata"]['environment']
else:
test_dict['environment'] = "others"
get_main_intent = True
else:
if get_main_intent and intent_name != None:
test_dict['main_intent'] = intent_name
support_action_list.append(test_dict)
test_dict = {}
get_main_intent = False
if i == len(support_chat_data_event_data):
data = json.loads(event_data[2])
if "timestamp" in data and "sender_id" in data:
delete_query = "DELETE FROM dashboard_api_logDetails where type='Support and Live Chat Details'"
self.psdatabase.connect_execute(env.delete_query_type,
dbQuery=delete_query)
insert_fallback_details = """INSERT INTO dashboard_api_logDetails ( sender_id, timestamp,type,table_id)values(%s,%s,%s,%s)"""
data = (data["sender_id"], data["timestamp"],
"Support and Live Chat Details", event_data[0])
self.psdatabase.connect_execute(
env.insert_query_type,
dbQuery=insert_fallback_details,
params=data)
i = i + 1
try:
insert_support_livefeedback = """INSERT INTO getSupportAndLiveChatDetails ( sender_id,event,timestamp, text,table_id ,user_intent, confidence, environment,main_intent)
values(%s,%s,%s,%s,%s,%s,%s,%s,%s)"""
for list in support_action_list:
live_feedback = []
for val in list.values():
live_feedback.append(val)
self.psdatabase.connect_execute(
env.insert_query_type,
dbQuery=insert_support_livefeedback,
params=live_feedback)
except Exception as e:
print("Exception occured")
print(e)
return support_action_list
def preparelikeAndDislike(self, like_and_dislike_data):
like_dislike_list = []
user_dict = {}
get_main_intent = False
main_intent = None
like_dislike = None
test_dict = {}
i = 1
for event_data in like_and_dislike_data:
table_id = event_data[0]
intent_type = event_data[1]
intent_name = event_data[2]
action_name = event_data[3]
data_dictionary = json.loads(event_data[4])
if intent_type == 'slot' and action_name == 'confirm':
for data in data_dictionary:
if data == 'value':
if data_dictionary['value'] == "true":
like_dislike = "like"
elif data_dictionary['value'] == "false":
like_dislike = "dislike"
if data_dictionary["event"] == 'user':
wanted_keys = ("sender_id", "name", "event",
"latest_event_time", "text", "timestamp")
dictfilt = lambda x, y: dict([(i, x[i]) for i in x
if i in set(y)])
user_dict = dictfilt(data_dictionary, wanted_keys)
user_dict['table_id'] = table_id
if "parse_data" in data_dictionary and "metadata" in data_dictionary:
user_dict['intent'] = data_dictionary['parse_data'][
'intent']['name']
user_dict['confidence'] = data_dictionary['parse_data'][
'intent']['confidence']
if 'environment' in data_dictionary["metadata"]:
user_dict['environment'] = data_dictionary["metadata"][
'environment']
else:
user_dict['environment'] = "others"
if action_name == 'utter_like_dislike':
if "event" in data_dictionary:
if data_dictionary["event"] == 'action':
wanted_keys = ("sender_id", "latest_event_time",
"text", "timestamp")
dictfilt = lambda x, y: dict([(i, x[i]) for i in x
if i in set(y)])
test_dict = dictfilt(data_dictionary, wanted_keys)
action_table_id = table_id
if bool(user_dict):
if test_dict['sender_id'] == user_dict[
'sender_id']:
test_dict['table_id'] = table_id
test_dict['like_dislike'] = like_dislike
if "environment" in user_dict:
test_dict['environment'] = user_dict[
'environment']
if "parse_data" in data_dictionary:
test_dict['user_intent'] = data_dictionary[
'parse_data']['intent']['name']
test_dict['confidence'] = data_dictionary[
'parse_data']['intent']['confidence']
# to get the main intent in the upcoming iterations
get_main_intent = True
like_dislike = None
else:
if get_main_intent and intent_name != None:
test_dict['main_intent'] = intent_name
like_dislike_list.append(test_dict)
test_dict = {}
get_main_intent = False
if i == len(like_and_dislike_data):
data = json.loads(event_data[4])
if "timestamp" in data and "sender_id" in data:
delete_query = "DELETE FROM dashboard_api_logDetails where type='Like and Dislike'"
self.psdatabase.connect_execute(env.delete_query_type,
dbQuery=delete_query)
insert_fallback_details = """INSERT INTO dashboard_api_logDetails ( sender_id, timestamp,type,table_id)values(%s,%s,%s,%s)"""
data = (data["sender_id"], data["timestamp"],
"Like and Dislike", event_data[0])
self.psdatabase.connect_execute(
env.insert_query_type,
dbQuery=insert_fallback_details,
params=data)
i = i + 1
try:
insert_like_dislike = """INSERT INTO getLikeDislikeDetails ( sender_id, timestamp, table_id, like_dislike, environment, main_intent)
values(%s,%s,%s,%s,%s,%s)"""
for list in like_dislike_list:
if not (("like_dislike", None) in list.items()):
like_dislike = []
for val in list.values():
like_dislike.append(val)
self.psdatabase.connect_execute(
env.insert_query_type,
dbQuery=insert_like_dislike,
params=like_dislike)
except Exception as e:
print("Exception occured")
print(e)
return like_dislike_list
def prepareData(self, conversation_data, conversation_event_data):
conversation_list = []
for data in conversation_data:
inner_dictionary = {}
inner_dictionary['sender_id'] = data[0]
inner_dictionary['latest_event_time'] = data[1]
conversation_list.append(inner_dictionary)
conversation_event_list = []
env_dict = {}
i = 1
for conversation in conversation_event_data:
table_id = conversation[0]
inner_dictionary = json.loads(conversation[1])
if "event" in inner_dictionary:
if inner_dictionary["event"] == 'bot':
wanted_keys = ("sender_id", "name", "event", "confidence",
"environment", "latest_event_time", "text",
"timestamp")
dictfilt = lambda x, y: dict([(i, x[i]) for i in x
if i in set(y)])
final_dict = dictfilt(inner_dictionary, wanted_keys)
final_dict["table_id"] = table_id
if 'env' in env_dict:
final_dict['environment'] = env_dict['env']
else:
final_dict['environment'] = 'others'
conversation_event_list.append(final_dict)
# name and confidence are present only incase of users
elif inner_dictionary["event"] == 'user':
wanted_keys = ("sender_id", "name", "event", "confidence",
"environment", "latest_event_time", "text",
"timestamp")
dictfilt = lambda x, y: dict([(i, x[i]) for i in x
if i in set(y)])
final_dict = dictfilt(inner_dictionary, wanted_keys)
final_dict["table_id"] = table_id
if final_dict['text'] == '/home' or final_dict[
'text'] == '/restart':
pass
else:
if "parse_data" in inner_dictionary:
final_dict['name'] = inner_dictionary[
'parse_data']['intent']['name']
final_dict['confidence'] = inner_dictionary[
'parse_data']['intent']['confidence']
if 'environment' in inner_dictionary["metadata"]:
final_dict['environment'] = inner_dictionary[
"metadata"]['environment']
env_dict['env'] = inner_dictionary["metadata"][
'environment']
else:
final_dict['environment'] = "others"
env_dict['env'] = "others"
conversation_event_list.append(final_dict)
if i == len(conversation_event_data):
data = json.loads(conversation[1])
if "timestamp" in data and "sender_id" in data:
delete_query = "DELETE FROM dashboard_api_logDetails where type='messages'"
self.psdatabase.connect_execute(env.delete_query_type,
dbQuery=delete_query)
insert_fallback_details = """INSERT INTO dashboard_api_logDetails ( sender_id, timestamp,type,table_id)values(%s,%s,%s,%s)"""
data = (data["sender_id"], data["timestamp"], "messages",
conversation[0])
self.psdatabase.connect_execute(
env.insert_query_type,
dbQuery=insert_fallback_details,
params=data)
i = i + 1
for convObj in conversation_list:
for eventObj in conversation_event_list:
if convObj['sender_id'] == eventObj['sender_id']:
eventObj['latest_event_time'] = convObj[
'latest_event_time']
try:
for list in conversation_event_list:
messages = []
for key, val in list.items():
if len(list.items()) == 9:
insert_support_livefeedback = """INSERT INTO getmessages ( sender_id,event,timestamp, text,table_id ,name,confidence,environment,latest_event_time)
values(%s,%s,%s,%s,%s,%s,%s,%s,%s)"""
if key in [
'sender_id', 'event', 'timestamp', 'text',
'table_id', 'environment', 'latest_event_time',
'name', 'confidence'
]:
messages.append(val)
elif len(list.items()) == 7:
insert_support_livefeedback = """INSERT INTO getmessages ( sender_id,event,timestamp, text,table_id ,environment,latest_event_time)
values(%s,%s,%s,%s,%s,%s,%s)"""
messages.append(val)
self.psdatabase.connect_execute(
env.insert_query_type,
dbQuery=insert_support_livefeedback,
params=messages)
except Exception as e:
print("Exception occured")
print(e)
return conversation_event_list
def __init__(self):
self._connectionDB = Utility()
self.dbProcessUtil = DBProcessUtil()
class OnBoarding:
def __init__(self):
self._connectionDB = Utility()
self.dbProcessUtil = DBProcessUtil()
def insertEmployeeData(self, employee):
fields, values = self.validateFields(employee, False)
print(values)
if fields == None or values == None or (type(values) == list
and len(values) == 0):
return {"message": "No records to insert", "type": "E"}
insertQuery = f""" INSERT INTO employee {str(tuple(fields)).replace("'", "")} VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"""
res = self._connectionDB.connect_execute(insertQuery, tuple(values))
msg, mType = self.dbProcessUtil.handleResponses(res, "INSERT 0 1")
if mType == "S":
return {"message": "Employee records inserted", "type": "S"}
return {"message": msg, "type": "E"}
def deleteEmployeeData(self, id):
postgres_delete_employee = str(
"""update employee set isactive=0 where empid={0}""").format(id)
self._connectionDB.connect_execute(postgres_delete_employee)
return {
"message": "Employee records deleted successfully",
"type": "S"
}
def updateEmployee(self, employee, id):
fields, values = self.validateFields(employee, True)
if id == None or fields == None or values == None or (
type(values) == list and len(values) == 0):
return {"message": "No records to update", "type": "E"}
updateQuery = f"""update employee set {fields} where empid=%s"""
values.append(id)
res = self._connectionDB.connect_execute(updateQuery, values)
msg, mType = self.dbProcessUtil.handleResponses(res, "UPDATE 1")
if mType == "S":
return {"message": "Employee records updated", "type": "S"}
return {"message": msg, "type": "E"}
def valudateDate(self, dte):
if dte != None and dte != "":
return datetime.datetime.fromtimestamp(
int(dte)).strftime("%Y-%m-%d")
return None
def displayEmployee(self, id=None):
if id is None:
selectEmployee = """select * from employee"""
else:
selectEmployee = str(
"""select * from employee where empid={0}""").format(id)
return json.loads(
json.dumps(self._connectionDB.connect_execute(selectEmployee),
default=self.myconverter))
def myconverter(sefl, o):
if isinstance(o, datetime.date):
return o.__str__()
def readUploadFile(self, fileString):
try:
reader = csv.DictReader(io.StringIO(str(fileString, 'utf-8')))
json_data = json.loads(json.dumps(list(reader)))
for dta in json_data:
res = self.insertEmployeeData(dta)
return res
except Exception as error:
print(f'error while connect to PostgreSQL : ' f'{error}')
return {"message": f'{error}', "type": 'E'}
def validateFields(self, emp, isUpdate=True):
if type(emp) != dict:
return None, None
keys = [
"bandcode", "banddescription", "businessunitname", "dateofbirth",
"dateofjoining", "dateofwedding", "department", "email", "empid",
"employeebandid", "employeefullname", "firstname", "gender",
"isactive", "lastname", "location", "managerid", "managername",
"middlename", "mobileno", "username"
]
validateFields = {"dateofjoining": self.valudateDate}
return self.dbProcessUtil.validateFields(emp, keys, isUpdate,
validateFields)