def __init__(self,data={}):
print("Initializing JirigoTicket")
print(f'In for Create/Update Ticket **** :{data}')
self.ticket_int_id = data.get('ticket_int_id')
self.jdb=JirigoDBConn()
self.summary = data.get('summary','')
self.description = data.get('description','')
self.severity = data.get('severity','')
self.priority = data.get('priority','')
self.issue_type = data.get('issue_type','')
self.issue_status = data.get('issue_status','')
self.is_blocking = data.get('is_blocking','N')
self.environment = data.get('environment','')
self.channel = data.get('channel','')
self.created_by = data.get('created_by','')
self.created_date = datetime.datetime.now()
self.modified_by = data.get('modified_by','')
self.modified_date = datetime.datetime.now()
self.reported_by = data.get('reported_by','')
self.reported_date = data.get('reported_date',datetime.datetime.now())
self.ticket_no=data.get('ticket_no','-')
self.project_name=data.get('project_name','')
self.project_id=data.get('project_id','')
self.assignee_name=data.get('assignee_name','')
self.module=data.get('module','')
self.assignee_id = data.get('assignee_id','')
self.row_hash = data.get('row_hash','')
self.logger=Logger()
def __init__(self,data={}):
print("Initializing JirigoTodos")
print(f'In for Create/Update/Delete todos ****')
pprint(data)
self.project_id = data.get('project_id')
self.user_id = data.get('user_id')
self.todo_id = data.get('todo_id')
self.label_id = data.get('label_id')
self.todo_label = data.get('todo_label')
self.end_date = datetime.datetime.now() if data.get('end_date') == None else data.get('end_date')
self.todo_text = data.get('todo_text')
self.todo_status= data.get('todo_status')
self.category_id= data.get('category_id')
self.category= data.get('category')
self.category_color= data.get('category_color')
self.created_by= data.get('created_by')
self.modified_by= data.get('modified_by')
self.interval_days=data.get('interval_days')
self.created_date=datetime.datetime.now()
self.modified_date=datetime.datetime.now()
# self.limit=data.get('limit',5)
# self.offset=data.get('offset',0)
self.limit=10000000
self.offset=0
self.jdb=JirigoDBConn()
self.logger=Logger()
def __init__(self, data={}):
print("Initializing JirigoTask")
print(f'In for Create/Update Task ****')
pprint(data)
self.task_int_id = data.get('task_int_id')
self.jdb = JirigoDBConn()
self.summary = data.get('summary', '')
self.description = data.get('description', '')
self.severity = data.get('severity', '')
self.priority = data.get('priority', '')
self.issue_type = data.get('issue_type', '')
self.issue_status = data.get('issue_status', '')
self.is_blocking = data.get('is_blocking', 'N')
self.environment = data.get('environment', '')
self.created_by = data.get('created_by', '')
self.created_date = datetime.datetime.now()
self.modified_by = data.get('modified_by', '')
self.modified_date = datetime.datetime.now()
self.reported_by = data.get('reported_by')
self.reported_date = data.get('reported_date', datetime.datetime.now())
self.task_no = data.get('task_no', '-')
self.project_name = data.get('project_name', '')
self.project_id = data.get('project_id', '')
self.assignee_name = data.get('assignee_name', '')
self.module_name = data.get('module_name', '')
self.estimated_time = 0 # At the time of task creation est is 0
self.start_date = data.get('start_date', None)
self.end_date = data.get('end_date', None)
self.row_hash = data.get('row_hash', None)
self.assignee_id = data.get('assignee_id', None)
self.task_estimates = data.get('task_estimates', None)
self.logger = Logger()
def __init__(self, data={}):
print("Initializing JirigoTicketDashboard")
print(f'In for Create Dashboard **** :{data}')
self.project_id = data.get('project_id', 0)
self.last_n_days = data.get('last_n_days', 0)
self.jdb = JirigoDBConn()
self.logger = Logger()
def __init__(self, data={}):
print("Initializing JirigoMenus")
self.project_id = data.get('project_id', '')
self.start_date = data.get('start_date', '')
self.end_date = data.get('end_date', '')
self.jdb = JirigoDBConn()
self.logger = Logger()
def __init__(self, data):
print("Initializing JirigoUsers")
pprint(data)
self.ticket_no = data.get('ticket_no', '')
self.comment = data.get('comment', '')
self.created_by = data.get('created_by', '1')
self.created_date = data.get('created_date', datetime.datetime.now())
self.jdb = JirigoDBConn()
self.logger = Logger()
def __init__(self, data={}):
print("Initializing JirigoWorkflow")
print(f'In for Create/Update/Delete Workflow ****')
pprint(data)
self.project_id = data.get('project_id')
self.workflow_id = data.get('workflow_id')
self.workflow_name = data.get('workflow_name')
self.jdb = JirigoDBConn()
self.logger = Logger()
def __init__(self, data={}):
print("Initializing JirigoGlobalSearch")
print(f'In for JirigoGlobalSearch **** :{data}')
self.search_text = data.get('search_text')
self.created_by = data.get('created_by')
self.created_date = datetime.datetime.now()
self.modified_by = data.get('modified_by')
self.modified_date = datetime.datetime.now()
self.jdb = JirigoDBConn()
self.logger = Logger()
def __init__(self, data):
print("Initializing JirigoTicketLogTime")
pprint(data)
self.ticket_no = data.get('ticket_no', '')
self.activity = data.get('activity', '')
self.actual_time_spent = data.get('actual_time_spent', 0)
self.other_activity_comment = data.get('other_activity_comment', '')
self.timelog_comment = data.get('timelog_comment', '')
self.time_spent_by = data.get('time_spent_by', '1')
self.actual_date = data.get('actual_date', datetime.datetime.now())
self.jdb = JirigoDBConn()
self.logger = Logger()
def __init__(self, data={}):
print("Initializing JirigoLinkTaskTicket")
pprint(data)
self.jdb = JirigoDBConn()
self.project_id = data.get('project_id', 0)
self.search_term = data.get('search_term', 0)
self.created_by = data.get('created_by')
self.created_date = datetime.datetime.now()
#-----------
self.item_no = data.get('item_no', '')
self.relationship = data.get('relationship', '')
self.related_items = data.get('related_items', '')
#-----------
self.logger = Logger()
def __init__(self, data=None):
self.jdb = JirigoDBConn()
self.logger = Logger()
self.query = ""
self.project_abbr = data.get('project_abbr', None)
self.chatObj = Chat(pairs, reflections)
self.lemmer = WordNetLemmatizer()
self.punctuations = dict(
(ord(punctuation), None) for punctuation in string.punctuation)
self.tag_map = defaultdict(lambda: wn.NOUN)
self.tag_map['J'] = wn.ADJ
self.tag_map['V'] = wn.VERB
self.tag_map['R'] = wn.ADV
def __init__(self,data={}):
self.project_id=data.get('project_id',0)
self.project_name=data.get('project_name',0)
self.ref_category=data.get('ref_category',None)
self.ref_name=data.get('ref_name',None)
self.ref_value=data.get('ref_value',None)
self.ref_id=data.get('ref_id',None)
self.order_id=data.get('order_id',None)
self.is_active=data.get('is_active',None)
self.created_by=data.get('created_by',1)
self.created_date = datetime.datetime.now()
self.modified_by=data.get('modified_by',1)
self.modified_date = datetime.datetime.now()
self.jdb=JirigoDBConn()
self.logger=Logger()
def __init__(self,data={}):
print("Initializing JirigoVacations")
print(f'In for Create/Update/Delete vacations ****')
pprint(data)
self.project_id = data.get('project_id')
self.vacation_id = data.get('vacation_id')
self.user_id = data.get('user_id')
self.start_date = data.get('start_date')
self.end_date = data.get('end_date')
self.input_date = data.get('input_date')
self.description = data.get('description')
self.created_date=datetime.datetime.now()
self.modified_date=datetime.datetime.now()
self.jdb=JirigoDBConn()
self.logger=Logger()
def __init__(self, data):
print("Initializing JirigoProject")
pprint(data)
self.user_id = data.get('user_id', 0)
self.project_id = data.get('project_id', 0)
self.project_name = data.get('project_name')
self.parent_project_id = data.get('parent_project_id', 0)
self.project_abbr = data.get('project_abbr')
self.project_type = data.get('project_type')
self.created_by = data.get('created_by')
self.created_date = datetime.datetime.now()
self.modified_by = None
self.modified_date = None
self.is_active = data.get('is_active', 'Y')
self.jdb = JirigoDBConn()
self.logger = Logger()
def __init__(self, data):
print("Initializing JirigoDBActions")
pprint(data)
self.jdb = JirigoDBConn()
self.logger = Logger()
self._sql_query = ''
self._query_params = ''
def __init__(self,data={}):
print("Initializing JirigoRoles")
self.project_id=data.get('project_id','')
self.project_name=data.get('project_name','')
self.role_id=data.get('role_id','')
self.workflow_id=data.get('workflow_id','')
self.role_name=data.get('role_name','')
self.user_id=data.get('user_id','')
self.new_roles_values=data.get('new_roles_values','')
self.is_active=data.get('is_active','')
self.project_name=data.get('project_name','')
self.created_by = data.get('created_by')
self.created_date = datetime.datetime.now()
self.modified_by=data.get('modified_by')
self.modified_date=datetime.datetime.now()
self.jdb=JirigoDBConn()
self.logger=Logger()
def __init__(self, data={}):
print("Initializing JirigoSprints")
print(f'In for Create Sprints **** :{data}')
self.sprint_name = data.get('sprint_name', '')
self.project_name = data.get('project_name', '')
self.created_by = data.get('created_by')
self.created_date = datetime.datetime.now()
self.sprint_tasks = data.get('sprint_tasks')
self.sprint_status = data.get('sprint_status')
self.sprint_id = data.get('sprint_id')
self.modified_by = data.get('modified_by')
self.modified_date = datetime.datetime.now()
self.start_date = data.get('start_date')
self.end_date = data.get('end_date')
self.num_devs = data.get('num_devs')
self.jdb = JirigoDBConn()
self.logger = Logger()
def __init__(self, data={}):
print("Initializing JirigoSprints")
self.workflow_name = data.get('workflow_name', '')
self.workflow_id = data.get('workflow_id', 0)
self.project_id = data.get('project_id', '')
self.role_id = data.get('role_id', '')
self.current_status = data.get('current_status', '')
self.ref_category = data.get('ref_category', '')
self.project_name = data.get('project_name', '')
self.workflow_type = data.get('workflow_type', '')
self.next_allowed_statuses = data.get('next_allowed_statuses', '')
self.step_full_details = data.get('step_full_details', '')
self.created_by = data.get('created_by')
self.created_date = datetime.datetime.now()
self.modified_by = data.get('modified_by')
self.modified_date = datetime.datetime.now()
self.jdb = JirigoDBConn()
self.logger = Logger()
def __init__(self, data):
print("Initializing JirigoUsers")
self.user_id = data.get('user_id', 0)
self.first_name = data.get('first_name', '')
self.last_name = data.get('last_name', '')
self.email = data.get('email', '')
self.is_active = data.get('is_active', '')
self.salt = data.get('salt', '')
self.password = data.get('password', '')
self.created_by = data.get('created_by', '')
self.created_date = datetime.datetime.now()
self.modified_by = data.get('modified_by', 0)
self.modified_date = datetime.datetime.now()
self.assigned_projects = data.get('assigned_projects', [])
self.role_id = data.get('role_id', '')
self.project_id = data.get('project_id', '')
self.current_route = data.get('current_route', '')
self.jdb = JirigoDBConn()
self.logger = Logger()
def __init__(self, data={}):
print("Initializing JirigoMenus")
self.project_id = data.get('project_id', '')
self.project_name = data.get('project_name', '')
self.role_id = data.get('role_id', '')
self.menu_id = data.get('menu_id', '')
self.new_menu_items = data.get('new_menu_items', '')
self.workflow_id = data.get('workflow_id', '')
self.role_name = data.get('role_name', '')
self.is_active = data.get('is_active', '')
self.project_name = data.get('project_name', '')
self.created_by = data.get('created_by')
self.created_date = datetime.datetime.now()
self.modified_by = data.get('modified_by')
self.modified_date = datetime.datetime.now()
self.current_user_id = data.get('current_user_id', 0)
self.num_rows = data.get('num_rows', 10)
self.jdb = JirigoDBConn()
self.logger = Logger()
class JirigoTaskDashboard(object):
def __init__(self, data={}):
print("Initializing JirigoTaskDashboard")
print(f'In for Create Dashboard **** :{data}')
# self.task_no = data.get('task_no')
self.jdb = JirigoDBConn()
self.logger = Logger()
def get_task_summaries(self):
response_data = {}
self.logger.debug("Inside get_task_audit")
query_sql = """
WITH t AS
(SELECT 'issueStatus' col_header,
issue_status col_ref_name,
count(*) cnt
FROM ttasks t
GROUP BY issue_status
UNION ALL SELECT 'issueType' col_header,
t.issue_type,
count(*) cnt
FROM
(SELECT CASE issue_type
WHEN 'Bug' THEN 'Bug'
ELSE 'Others'
END AS issue_type
FROM ttasks t2) AS t
GROUP BY issue_type
UNION ALL SELECT 'Severity' col_header,
severity,
count(*) cnt
FROM ttasks t
WHERE SEVERITY IN ('High',
'Critical')
GROUP BY severity)
SELECT json_object_agg(col_header||col_ref_name, cnt)
FROM t ;
"""
self.logger.debug(f'Select : {query_sql}')
try:
print('-' * 80)
cursor = self.jdb.dbConn.cursor()
cursor.execute(query_sql)
json_data = cursor.fetchone()[0]
row_count = cursor.rowcount
self.logger.debug(
f'Select get_task_audit Success with {row_count} row(s) Task ID {json_data}'
)
response_data['dbQryStatus'] = 'Success'
response_data['dbQryResponse'] = json_data
return response_data
except (Exception, psycopg2.Error) as error:
if (self.jdb.dbConn):
print(f'Error While getting Task Audit {error}')
raise
class JirigoTicketAudit(object):
def __init__(self,data={}):
print("Initializing JirigoTicketAudit")
print(f'In for Create TicketAudit **** :{data}')
self.ticket_no = data.get('ticket_no')
self.jdb=JirigoDBConn()
self.logger=Logger()
def get_ticket_audit(self):
response_data={}
self.logger.debug("Inside get_ticket_audit")
query_sql="""
WITH t AS (
SELECT ticket_no,
column_name,
display_column_name,
CASE column_name
WHEN 'estimated_time'
THEN (round(old_value::NUMERIC(5)/60,2))::text
ELSE old_value::text
END as old_value,
CASE column_name
WHEN 'estimated_time'
THEN (round(new_value::NUMERIC(5)/60,2))::text
ELSE new_value::text
END as new_value,
get_user_name(created_by) created_by,
to_char(created_date, 'DD-Mon-YYYY HH24:MI:SS') created_date
FROM htickets
where ticket_no=%s
order by hticket_int_id desc limit 15
)
SELECT json_agg(t) from t;
"""
values=(self.ticket_no,)
self.logger.debug(f'Select : {query_sql} values {values}')
try:
print('-'*80)
cursor=self.jdb.dbConn.cursor()
cursor.execute(query_sql,values)
json_data=cursor.fetchone()[0]
row_count=cursor.rowcount
self.logger.debug(f'Select get_ticket_audit Success with {row_count} row(s) Ticket ID {json_data}')
response_data['dbQryStatus']='Success'
response_data['dbQryResponse']=json_data
return response_data
except (Exception, psycopg2.Error) as error:
if(self.jdb.dbConn):
print(f'Error While getting Ticket Audit {error}')
raise
class JirigoGlobalSearch(object):
def __init__(self, data={}):
print("Initializing JirigoGlobalSearch")
print(f'In for JirigoGlobalSearch **** :{data}')
self.search_text = data.get('search_text')
self.created_by = data.get('created_by')
self.created_date = datetime.datetime.now()
self.modified_by = data.get('modified_by')
self.modified_date = datetime.datetime.now()
self.jdb = JirigoDBConn()
self.logger = Logger()
def global_search(self):
response_data = {}
self.logger.debug(" Inside global_search")
query_sql = """
WITH tc AS (SELECT item_no,summary,item_type
FROM v_all_tickets_tasks vatt
WHERE to_tsvector(item_no||'-'||summary||description) @@ plainto_tsquery (%s)
LIMIT 10
)
select json_agg(tc) from tc;
"""
values = (self.search_text, )
self.logger.debug(f'Select : {query_sql} values {values}')
try:
print('-' * 80)
cursor = self.jdb.dbConn.cursor()
cursor.execute(query_sql, values)
json_data = cursor.fetchone()[0]
row_count = cursor.rowcount
self.logger.debug(
f'Select Success with {row_count} row(s) global_search {json_data}'
)
response_data['dbQryStatus'] = 'Success'
response_data['dbQryResponse'] = json_data
return response_data
except (Exception, psycopg2.Error) as error:
if (self.jdb.dbConn):
print(f'Error While global_search {error}')
raise
class JirigoUsers(object):
def __init__(self, data):
print("Initializing JirigoUsers")
self.user_id = data.get('user_id', 0)
self.first_name = data.get('first_name', '')
self.last_name = data.get('last_name', '')
self.email = data.get('email', '')
self.is_active = data.get('is_active', '')
self.salt = data.get('salt', '')
self.password = data.get('password', '')
self.created_by = data.get('created_by', '')
self.created_date = datetime.datetime.now()
self.modified_by = data.get('modified_by', 0)
self.modified_date = datetime.datetime.now()
self.assigned_projects = data.get('assigned_projects', [])
self.role_id = data.get('role_id', '')
self.project_id = data.get('project_id', '')
self.current_route = data.get('current_route', '')
self.jdb = JirigoDBConn()
self.logger = Logger()
@classmethod
def for_create_update_users(cls, data):
print("-" * 40)
print(f'In for_create_update_users :{data}')
print("-" * 40)
return cls(data)
def register_user(self):
response_data = {}
print("Inside Create User")
self.salt = JirigoUsers.generate_salt()
insert_sql = """ INSERT INTO TUSERS(first_name,last_name,email,salt,password,
created_by,created_date,is_active)
VALUES (%s,%s,%s,%s,%s,%s,%s,%s) returning user_id;
"""
values = (
self.first_name,
self.last_name,
self.email,
self.salt,
JirigoUsers.get_password_digest(self.password, self.salt),
1,
datetime.datetime.today(),
self.is_active,
)
print(f'Insert : {insert_sql} {values}')
try:
print('-' * 80)
print(type(self.jdb.dbConn))
cursor = self.jdb.dbConn.cursor()
cursor.execute(insert_sql, values)
user_id = cursor.fetchone()[0]
self.jdb.dbConn.commit()
row_count = cursor.rowcount
print(
f'User Creation Success with {row_count} row(s) User ID {user_id}'
)
response_data['dbQryStatus'] = 'Success'
response_data['dbQryResponse'] = {"userId": user_id, "rowCount": 1}
return response_data
except (Exception, psycopg2.Error) as error:
if (self.jdb.dbConn):
print(f'Error While Creating User {error}')
raise
def create_user(self):
response_data = {}
print("Inside Create User")
self.salt = JirigoUsers.generate_salt()
insert_sql = """ INSERT INTO TUSERS(first_name,last_name,email,salt,password,
created_by,created_date,is_active)
VALUES (%s,%s,%s,%s,%s,%s,%s,%s) returning user_id;
"""
values = (
self.first_name,
self.last_name,
self.email,
self.salt,
JirigoUsers.get_password_digest(self.password, self.salt),
1,
datetime.datetime.today(),
self.is_active,
)
print(f'Insert : {insert_sql} {values}')
add_user_projects_sql = """
INSERT INTO TUSER_PROJECTS
(user_id,project_id,created_date,created_by,is_active,default_project)
VALUES (%s,%s,%s,%s,%s,%s);
"""
try:
print('-' * 80)
print(type(self.jdb.dbConn))
cursor = self.jdb.dbConn.cursor()
cursor.execute(insert_sql, values)
user_id = cursor.fetchone()[0]
print(f'self.assigned_projects {self.assigned_projects}')
print(f'User id is {user_id}')
default_project = 'Y'
for i in range(len(self.assigned_projects)):
print(f'Inserting project id {self.assigned_projects[i]}')
values = (
user_id,
self.assigned_projects[i],
self.created_date,
self.created_by,
'Y',
default_project,
)
print(
f'TUSER_PROJECTS Insert : {add_user_projects_sql} {values}'
)
cursor.execute(add_user_projects_sql, values)
default_project = 'N'
self.jdb.dbConn.commit()
row_count = cursor.rowcount
print(
f'User Creation Success with {row_count} row(s) User ID {user_id}'
)
response_data['dbQryStatus'] = 'Success'
response_data['dbQryResponse'] = {"userId": user_id, "rowCount": 1}
return response_data
except (Exception, psycopg2.Error) as error:
if (self.jdb.dbConn):
print(f'Error While Creating User {error}')
raise
def get_all_users(self):
response_data = {}
self.logger.debug("Inside get_all_users")
query_sql = """
WITH t AS (
select first_name,last_name,user_id,get_user_name(user_id) as name,email,
is_active,password_change_date
from tusers
order by first_name,last_name
)
SELECT json_agg(t) from t;
"""
self.logger.debug(f'Select : {query_sql}')
try:
print('-' * 80)
cursor = self.jdb.dbConn.cursor()
cursor.execute(query_sql)
json_data = cursor.fetchone()[0]
row_count = cursor.rowcount
self.logger.debug(
f'Select Success with {row_count} row(s) data {json_data}')
if (json_data == None):
response_data['dbQryStatus'] = 'No Data Found'
else:
response_data['dbQryStatus'] = 'Success'
response_data['dbQryResponse'] = json_data
return response_data
except (Exception, psycopg2.Error) as error:
if (self.jdb.dbConn):
print(f'Error While Select Users {error}')
raise
def get_user_details(self):
response_data = {}
self.logger.debug("Inside get_all_users")
query_sql = """
WITH t AS (
select user_id,first_name||'.'||last_name as name,email
from tusers
where is_active='Y'
and user_id=%s
)
SELECT json_agg(t) from t;
"""
values = (self.user_id, )
self.logger.debug(f'Select : {query_sql} Values {values}')
try:
print('-' * 80)
cursor = self.jdb.dbConn.cursor()
cursor.execute(query_sql, values)
json_data = cursor.fetchone()[0]
row_count = cursor.rowcount
self.logger.debug(
f'Select get_user_details Success with {row_count} row(s) data {json_data}'
)
if (json_data == None):
response_data['dbQryStatus'] = 'No Data Found'
else:
response_data['dbQryStatus'] = 'Success'
response_data['dbQryResponse'] = json_data
return response_data
except (Exception, psycopg2.Error) as error:
if (self.jdb.dbConn):
print(f'Error While Select get_user_details {error}')
raise
def get_user_names(self, user_name):
response_data = {}
self.logger.debug("Inside get_user_names")
query_sql = """
WITH t AS (
select user_id,get_user_name(user_id) as name,email
from tusers
where is_active='Y'
and lower(first_name||'.'||last_name) like lower(%s)
)
SELECT json_agg(t) from t;
"""
user_name = str(user_name).replace('%', '####')
values = (f'{user_name}%', )
self.logger.debug(f'Select : {query_sql} Values {values}')
try:
print('-' * 80)
cursor = self.jdb.dbConn.cursor()
cursor.execute(query_sql, values)
json_data = cursor.fetchone()[0]
row_count = cursor.rowcount
self.logger.debug(
f'Select get_user_names Success with {row_count} row(s) data {json_data}'
)
if (json_data == None):
response_data['dbQryStatus'] = 'No Data Found'
else:
response_data['dbQryStatus'] = 'Success'
response_data['dbQryResponse'] = json_data
return response_data
except (Exception, psycopg2.Error) as error:
if (self.jdb.dbConn):
print(f'Error While Select get_user_names {error}')
raise
def update_user_details(self):
response_data = {}
self.logger.debug("Inside update_user_details")
update_sql = """
UPDATE TUSERS
SET first_name=%s,
last_name=%s,
email=%s,
modified_by=%s,
modified_date=%s
WHERE user_id=%s;
"""
values = (
self.first_name,
self.last_name,
self.email,
self.modified_by,
datetime.datetime.today(),
self.user_id,
)
self.logger.debug(f'update_user_details : {update_sql} {values}')
try:
print('-' * 80)
print(type(self.jdb.dbConn))
cursor = self.jdb.dbConn.cursor()
cursor.execute(update_sql, values)
self.jdb.dbConn.commit()
response_data['dbQryStatus'] = 'Success'
response_data['dbQryResponse'] = {"rowUpdateCount": 1}
return response_data
except (Exception, psycopg2.Error) as error:
if (self.jdb.dbConn):
print(f'Error While Updating Ticket {error}')
raise
def validate_userid_password(self):
response_data = {}
self.logger.debug("Inside validate_userid_password")
try:
salt = self.get_user_salt()
except Exception as error:
print("Error while retrieving user Salt")
response_data['dbQryStatus'] = 'Failure'
response_data['dbQryResponse'] = 'Invalid User Id or Password'
return response_data
print(f'User Salt is {salt}')
query_sql = """
WITH t AS (
SELECT tu.user_id,get_user_name(tu.user_id) as user_name,
tu.email,tu.is_active,coalesce(tup.project_id,0) project_id,
get_proj_name(tup.project_id) project_name,
get_role_id_for_user(tu.user_id,tup.project_id) role_id,
get_role_name(get_role_id_for_user(tu.user_id,tup.project_id)) role_name
FROM tusers tu
LEFT OUTER JOIN tuser_projects tup
ON tu.user_id = tup.user_id
WHERE tu.email=%s
AND tu.password=%s
AND COALESCE (tup.default_project,'Y') ='Y'
)
SELECT json_agg(t) from t;
"""
values = (
self.email,
JirigoUsers.get_password_digest(self.password, salt),
)
self.logger.debug(f'Select : {query_sql} Values {values}')
try:
print('-' * 80)
cursor = self.jdb.dbConn.cursor()
cursor.execute(query_sql, values)
json_data = cursor.fetchone()[0]
row_count = cursor.rowcount
self.logger.debug(
f'Select validate_password Success with {row_count} row(s) data {json_data}'
)
if (json_data == None):
response_data['dbQryStatus'] = 'Failure'
response_data['dbQryResponse'] = 'Invalid User Id or Password'
else:
self.logger.debug(f'json_data[0] {json_data[0]}')
if (json_data[0]['is_active'] != 'Y'):
response_data['dbQryStatus'] = 'Failure'
response_data[
'dbQryResponse'] = 'Inactive User, Contact Administrator.'
elif (json_data[0]['project_id'] == 0):
response_data['dbQryStatus'] = 'Failure'
response_data[
'dbQryResponse'] = 'Active User, but no Project Assigned. Contact Adminstrator.'
else:
response_data['dbQryStatus'] = 'Success'
response_data['dbQryResponse'] = json_data
return response_data
except (Exception, psycopg2.Error) as error:
if (self.jdb.dbConn):
print(f'Error While Select validate_password {error}')
raise
def set_user_password(self):
response_data = {}
self.logger.debug("Inside set_password")
update_sql = """
UPDATE TUSERS
SET password=%s,
salt=%s,
modified_by=%s,
modified_date=%s,
password_change_date=%s
WHERE (user_id=%s
OR email=%s)
"""
salt = JirigoUsers.generate_salt()
values = (
JirigoUsers.get_password_digest(self.password, salt),
salt,
self.modified_by,
datetime.datetime.now(),
datetime.datetime.now(),
self.user_id,
self.email,
)
self.logger.debug(f'set_password : {update_sql} {values}')
try:
print('-' * 80)
print(type(self.jdb.dbConn))
cursor = self.jdb.dbConn.cursor()
cursor.execute(update_sql, values)
self.jdb.dbConn.commit()
response_data['dbQryStatus'] = 'Success'
response_data['dbQryResponse'] = {"rowUpdateCount": 1}
return response_data
except (Exception, psycopg2.Error) as error:
print(f'Error While set_user_password{error}')
raise
def get_user_salt(self):
self.logger.debug("Inside get_user_names")
query_sql = """
select salt
from tusers
WHERE ( email=%s
OR user_id=%s )
"""
values = (
self.email,
self.user_id,
)
self.logger.debug(f'Select : {query_sql} Values {values}')
try:
print('-' * 80)
cursor = self.jdb.dbConn.cursor()
cursor.execute(query_sql, values)
data = cursor.fetchone()[0]
row_count = cursor.rowcount
self.logger.debug(
f'Select get_user_salt Success with {row_count} row(s) data {data}'
)
return data
except (Exception, psycopg2.Error) as error:
print(f'Error While Select get_user_salt {error}')
raise
def authenticate_route_for_user(self):
response_data = {}
self.logger.debug("Inside authenticate_route_for_user")
query_sql = """
WITH t AS (
SELECT *
FROM v_user_projects_role_menu vuprm
WHERE vuprm.project_id =%s
AND user_id=%s
AND role_id=%s
AND (path = %s )
)
SELECT json_agg(t) from t;
"""
values = (
self.project_id,
self.user_id,
self.role_id,
self.current_route,
)
self.logger.debug(f'Select : {query_sql} Values {values}')
try:
print('-' * 80)
cursor = self.jdb.dbConn.cursor()
cursor.execute(query_sql, values)
json_data = cursor.fetchone()[0]
print("=^^=" * 30)
print(json_data)
print("=^^=" * 30)
row_count = cursor.rowcount
self.logger.debug(
f'Select authenticate_route_for_user Success with {row_count} row(s) data {json_data}'
)
if (json_data == None):
response_data['dbQryStatus'] = 'Failure'
response_data[
'dbQryResponse'] = 'Access Denied To Current Route'
else:
self.logger.debug(f'json_data[0] {json_data[0]}')
response_data['dbQryStatus'] = 'Success'
response_data['dbQryResponse'] = 'Access Granted'
return response_data
except (Exception, psycopg2.Error) as error:
if (self.jdb.dbConn):
print(
f'Error While Select authenticate_route_for_user {error}')
raise
def get_all_valid_routes_for_user(self):
response_data = {}
self.logger.debug("Inside get_all_valid_routes_for_user")
query_sql = """
WITH t AS (
SELECT *
FROM v_user_projects_role_menu vuprm
WHERE vuprm.project_id =%s
AND user_id=%s
AND role_id=%s
)
SELECT json_agg(t) from t;
"""
values = (
self.project_id,
self.user_id,
self.role_id,
)
self.logger.debug(f'Select : {query_sql} Values {values}')
try:
print('-' * 80)
cursor = self.jdb.dbConn.cursor()
cursor.execute(query_sql, values)
json_data = cursor.fetchone()[0]
row_count = cursor.rowcount
self.logger.debug(
f'Select Success with {row_count} row(s) {json_data}')
response_data['dbQryStatus'] = 'Success'
response_data['dbQryResponse'] = json_data
return response_data
except (Exception, psycopg2.Error) as error:
if (self.jdb.dbConn):
print(f'Error While get_all_valid_routes_for_user {error}')
raise
def update_toggle_active_status(self):
response_data = {}
self.logger.debug("Inside update_toggle_active_status")
update_sql = """
UPDATE TUSERS
SET is_active=%s,
modified_by=%s,
modified_date=%s
WHERE user_id=%s;
"""
values = (
self.is_active,
self.modified_by,
self.modified_date,
self.user_id,
)
self.logger.debug(
f'update_toggle_active_status : {update_sql} {values}')
try:
print('-' * 80)
print(type(self.jdb.dbConn))
cursor = self.jdb.dbConn.cursor()
cursor.execute(update_sql, values)
self.jdb.dbConn.commit()
response_data['dbQryStatus'] = 'Success'
response_data['dbQryResponse'] = {"rowUpdateCount": 1}
return response_data
except (Exception, psycopg2.Error) as error:
if (self.jdb.dbConn):
print(f'Error While Updating User {error}')
raise
@staticmethod
def get_password_digest(p_password, salt, iterations=1000000, dklen=64):
# print(f'{p_password} {p_password.encode()} {bytes(p_password,"utf-8")}')
return pbkdf2_hmac('sha512', p_password.encode(), salt.encode(),
iterations, dklen)
@staticmethod
def generate_salt(salt_len=20):
return ''.join([
random.choice(string.ascii_letters + string.digits +
string.punctuation) for _ in range(salt_len)
])
def __init__(self, data={}):
print("Initializing JirigoTaskDashboard")
print(f'In for Create Dashboard **** :{data}')
# self.task_no = data.get('task_no')
self.jdb = JirigoDBConn()
self.logger = Logger()
class JirigoTicketLogTime(object):
def __init__(self, data):
print("Initializing JirigoTicketLogTime")
pprint(data)
self.ticket_no = data.get('ticket_no', '')
self.activity = data.get('activity', '')
self.actual_time_spent = data.get('actual_time_spent', 0)
self.other_activity_comment = data.get('other_activity_comment', '')
self.timelog_comment = data.get('timelog_comment', '')
self.time_spent_by = data.get('time_spent_by', '1')
self.actual_date = data.get('actual_date', datetime.datetime.now())
self.jdb = JirigoDBConn()
self.logger = Logger()
def create_timelog_entry(self):
response_data = {}
print("Inside Create Comment")
insert_sql = """ INSERT
INTO tticket_actuals(ticket_no,activity,time_spent,actual_date,
time_spent_by,other_activity_comment,timelog_comment)
VALUES (%s,%s,%s,%s,%s,%s,%s) returning actuals_id;
"""
values = (self.ticket_no, self.activity, self.actual_time_spent,
self.actual_date, self.time_spent_by,
self.other_activity_comment, self.timelog_comment)
self.logger.debug(f'Insert : {insert_sql} {values}')
try:
print('-' * 80)
print(type(self.jdb.dbConn))
cursor = self.jdb.dbConn.cursor()
cursor.execute(insert_sql, values)
actuals_id = cursor.fetchone()[0]
self.jdb.dbConn.commit()
row_count = cursor.rowcount
self.logger.debug(
f'Ticket Comment Creation Success with {row_count} row(s) User ID {actuals_id}'
)
response_data['dbQryStatus'] = 'Success'
response_data['dbQryResponse'] = {
"actualsId": actuals_id,
"rowCount": 1
}
return response_data
except (Exception, psycopg2.Error) as error:
if (self.jdb.dbConn):
self.logger.debug(f'Error create_timelog_entry {error}')
print(f'Error create_timelog_entry {error}')
raise
def get_timelog_entries_for_ticket(self):
response_data = {}
self.logger.debug("Inside get_timelog_entries_for_ticket")
query_sql = """
WITH t AS (
SELECT ticket_no,activity,time_spent,actual_date,
get_user_name(time_spent_by) time_spent_by,
other_activity_comment,timelog_comment
FROM tticket_actuals
WHERE ticket_no=%s
order by actual_date desc
)
SELECT json_agg(t) from t;
"""
values = (self.ticket_no, )
self.logger.debug(f'Select : {query_sql} Values {values}')
try:
print('-' * 80)
cursor = self.jdb.dbConn.cursor()
cursor.execute(query_sql, values)
json_data = cursor.fetchone()[0]
row_count = cursor.rowcount
self.logger.debug(
f'get_timelog_entries_for_ticket Select Success with {row_count} row(s) data {json_data}'
)
if (json_data == None):
response_data['dbQryStatus'] = 'No Data Found'
else:
response_data['dbQryStatus'] = 'Success'
response_data['dbQryResponse'] = json_data
return response_data
except (Exception, psycopg2.Error) as error:
if (self.jdb.dbConn):
self.logger.debug(
f'get_timelog_entries_for_ticket Error While Select Ticket Comments {error}'
)
print(
f'get_timelog_entries_for_ticket Error While Select Ticket Comments {error}'
)
raise
class JirigoProjects(object):
def __init__(self, data):
print("Initializing JirigoProject")
pprint(data)
self.user_id = data.get('user_id', 0)
self.project_id = data.get('project_id', 0)
self.project_name = data.get('project_name')
self.parent_project_id = data.get('parent_project_id', 0)
self.project_abbr = data.get('project_abbr')
self.project_type = data.get('project_type')
self.created_by = data.get('created_by')
self.created_date = datetime.datetime.now()
self.modified_by = None
self.modified_date = None
self.is_active = data.get('is_active', 'Y')
self.jdb = JirigoDBConn()
self.logger = Logger()
def create_project(self):
response_data = {}
self.logger.debug("Inside Create Project")
insert_sql = """ INSERT INTO TPROJECTS(project_name,project_abbr,project_type,created_by,created_date,is_active,workflow_id)
VALUES (%s,%s,%s,%s,%s,%s,1) returning project_id;
"""
values = (
self.project_name,
self.project_abbr,
self.project_type,
self.created_by,
self.created_date,
self.is_active,
)
print(f'Insert : {insert_sql} {values}')
insert_project_refs = """
INSERT INTO tref_master (ref_category ,ref_name ,ref_value ,is_active ,
created_by ,created_date ,order_id,project_id )
SELECT ref_category ,ref_name ,ref_value ,is_active ,
created_by ,%s ,order_id,%s
FROM tref_master
WHERE project_id=%s;
"""
try:
print('-' * 80)
print(type(self.jdb.dbConn))
cursor = self.jdb.dbConn.cursor()
cursor.execute(insert_sql, values)
project_id = cursor.fetchone()[0]
row_count = cursor.rowcount
self.logger.debug(
f'Insert Success with {row_count} row(s) New Project ID {project_id}'
)
values = (
self.created_date,
project_id,
self.parent_project_id,
)
cursor.execute(insert_project_refs, values)
# cursor.fetchone()[0]
row_count = cursor.rowcount
self.jdb.dbConn.commit()
self.logger.debug(
f'Insert References Success with {row_count} row(s) ')
response_data['dbQryStatus'] = 'Success'
response_data['dbQryResponse'] = {
"projectId": project_id,
"rowCount": 1
}
print("Going to sleep for 10 seconds")
time.sleep(10)
return response_data
except (Exception, psycopg2.Error) as error:
if (self.jdb.dbConn):
print(f'Error While Creating Project {error}')
self.logger.debug(f'Error While Creating Project {error}')
raise
def get_all_projects(self):
response_data = {}
self.logger.debug("Inside get_all_projects")
query_sql = """
WITH t AS (
select *
from tprojects
where is_active='Y'
)
SELECT json_agg(t) from t;
"""
self.logger.debug(f'Select : {query_sql}')
try:
print('-' * 80)
cursor = self.jdb.dbConn.cursor()
cursor.execute(query_sql)
json_data = cursor.fetchone()[0]
row_count = cursor.rowcount
self.logger.debug(
f'get_all_projects Select Success with {row_count} row(s) data {json_data}'
)
if (json_data == None):
response_data['dbQryStatus'] = 'No Data Found'
else:
response_data['dbQryStatus'] = 'Success'
response_data['dbQryResponse'] = json_data
return response_data
except (Exception, psycopg2.Error) as error:
if (self.jdb.dbConn):
print(f'Error While Select Projects {error}')
raise
def get_all_projects_for_user(self):
response_data = {}
self.logger.debug("Inside get_all_projects_for_user")
query_sql = """
WITH t AS (
SELECT t.user_id,t.email,tup.default_project ,
tp.project_name ,tp.project_id,tp.project_abbr,
get_user_name(t.user_id) user_name
FROM tuser_projects tup,tusers t ,tprojects tp
WHERE tup.project_id = tp.project_id
AND tup.user_id =t.user_id
AND t.user_id =%s
ORDER BY tp.project_id
)
SELECT json_agg(t) from t;
"""
values = (self.user_id, )
self.logger.debug(f'Select : {query_sql} values{values}')
try:
print('-' * 80)
cursor = self.jdb.dbConn.cursor()
cursor.execute(query_sql, values)
json_data = cursor.fetchone()[0]
row_count = cursor.rowcount
self.logger.debug(
f'get_all_projects_for_user Select Success with {row_count} row(s) data {json_data}'
)
if (json_data == None):
response_data['dbQryStatus'] = 'No Data Found'
else:
response_data['dbQryStatus'] = 'Success'
response_data['dbQryResponse'] = json_data
return response_data
except (Exception, psycopg2.Error) as error:
if (self.jdb.dbConn):
print(f'Error While Select get_all_projects_for_user {error}')
raise
def get_project_details(self):
response_data = {}
self.logger.debug("Inside get_project_details")
query_sql = """
WITH t AS (
SELECT tp.*
FROM tprojects tp
WHERE tp.project_id=%s
)
SELECT json_agg(t) from t;
"""
values = (self.project_id, )
self.logger.debug(f'Select : {query_sql} values{values}')
try:
print('-' * 80)
cursor = self.jdb.dbConn.cursor()
cursor.execute(query_sql, values)
json_data = cursor.fetchone()[0]
row_count = cursor.rowcount
self.logger.debug(
f'get_project_details Select Success with {row_count} row(s) data {json_data}'
)
if (json_data == None):
response_data['dbQryStatus'] = 'No Data Found'
else:
response_data['dbQryStatus'] = 'Success'
response_data['dbQryResponse'] = json_data
return response_data
except (Exception, psycopg2.Error) as error:
if (self.jdb.dbConn):
print(f'Error While Select get_all_projects_for_user {error}')
raise
class JirigoTicket(object):
def __init__(self,data={}):
print("Initializing JirigoTicket")
print(f'In for Create/Update Ticket **** :{data}')
self.ticket_int_id = data.get('ticket_int_id')
self.jdb=JirigoDBConn()
self.summary = data.get('summary','')
self.description = data.get('description','')
self.severity = data.get('severity','')
self.priority = data.get('priority','')
self.issue_type = data.get('issue_type','')
self.issue_status = data.get('issue_status','')
self.is_blocking = data.get('is_blocking','N')
self.environment = data.get('environment','')
self.channel = data.get('channel','')
self.created_by = data.get('created_by','')
self.created_date = datetime.datetime.now()
self.modified_by = data.get('modified_by','')
self.modified_date = datetime.datetime.now()
self.reported_by = data.get('reported_by','')
self.reported_date = data.get('reported_date',datetime.datetime.now())
self.ticket_no=data.get('ticket_no','-')
self.project_name=data.get('project_name','')
self.project_id=data.get('project_id','')
self.assignee_name=data.get('assignee_name','')
self.module=data.get('module','')
self.assignee_id = data.get('assignee_id','')
self.row_hash = data.get('row_hash','')
self.logger=Logger()
@classmethod
def for_create_update_ticket(cls,data):
print("-"*40)
print(f'In for_create_update_ticket Ticket :{data}')
print("-"*40)
return cls(data)
def create_ticket(self):
response_data={}
self.logger.debug("Inside Create Ticket")
insert_sql=""" INSERT INTO TTICKETS(ticket_no,summary,description,severity,priority,
issue_status,issue_type,environment,is_blocking,created_by,
created_date,reported_by,reported_date,assignee_id,project_id,module,channel)
VALUES (get_issue_no_by_proj(%s),%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,
get_user_id(%s),%s,get_user_id(%s),get_proj_id(%s),%s,%s) returning ticket_no;
"""
values=(self.project_name,self.summary,self.description,self.severity,self.priority,
"Open",self.issue_type,self.environment,self.is_blocking,self.created_by,
datetime.datetime.today(),self.reported_by,datetime.datetime.today(),
self.assignee_name,self.project_name,self.module,self.channel,)
self.logger.debug(f'Insert : {insert_sql} {values}')
try:
print('-'*80)
print(type(self.jdb.dbConn))
cursor=self.jdb.dbConn.cursor()
cursor.execute(insert_sql,values)
ticket_int_id=cursor.fetchone()[0]
self.jdb.dbConn.commit()
row_count=cursor.rowcount
self.logger.debug(f'Insert Success with {row_count} row(s) Ticket ID {ticket_int_id}')
response_data['dbQryStatus']='Success'
response_data['dbQryResponse']={"ticket_no":ticket_int_id,"rowCount":row_count}
return response_data
except (Exception, psycopg2.Error) as error:
if(self.jdb.dbConn):
print(f'Error While Creating Ticket {error}')
raise
def get_all_tickets(self):
response_data={}
self.logger.debug("Inside get_all_tickets")
query_sql="""
WITH t AS (
SELECT ticket_int_id,
ticket_no,
summary,
description,
issue_status,
issue_type,
severity,
priority,
environment,
is_blocking,
module,
channel,
get_user_name(created_by) created_by,
to_char(created_date, 'DD-Mon-YYYY HH24:MI:SS') created_date,
get_user_name(modified_by) modified_by,
to_char(created_date, 'DD-Mon-YYYY HH24:MI:SS') modified_date,
get_user_name(reported_by) reported_by,
to_char(created_date, 'DD-Mon-YYYY HH24:MI:SS') reported_date,
get_user_name(assignee_id) assigned_to
FROM ttickets
WHERE
project_id=COALESCE(%s,project_id) AND
(
created_by=COALESCE(%s,created_by) AND
COALESCE(assignee_id,-1)=COALESCE(%s,COALESCE(assignee_id,-1)) AND
COALESCE(modified_by,-1)=COALESCE(%s,COALESCE(modified_by,-1))
)
order by ticket_int_id
)
SELECT json_agg(t) from t;
"""
self.project_id = None if self.project_id == '' else self.project_id
self.assignee_id = None if self.assignee_id == '' else self.assignee_id
self.created_by = None if self.created_by == '' else self.created_by
self.modified_by = None if self.modified_by == '' else self.modified_by
values=(self.project_id,self.created_by,self.assignee_id,self.modified_by,)
self.logger.debug(f'Select : {query_sql} values {values}')
try:
print('-'*80)
cursor=self.jdb.dbConn.cursor()
cursor.execute(query_sql,values)
json_data=cursor.fetchone()[0]
row_count=cursor.rowcount
self.logger.debug(f'Select Success with {row_count} row(s) Ticket List {json_data}')
if json_data == None:
response_data['dbQryStatus']='Failure No Rows'
response_data['dbQryResponse']={}
else:
response_data['dbQryStatus']='Success'
response_data['dbQryResponse']=json_data
return response_data
except (Exception, psycopg2.Error) as error:
if(self.jdb.dbConn):
print(f'Error While Getting All Tickets {error}')
raise
def get_ticket_details(self):
self.logger.debug('Inside get_ticket_details')
response_data={}
query_sql="""
WITH t AS
(SELECT ticket_int_id,
ticket_no,
SUMMARY,
description,
issue_status,
issue_type,
severity,
priority,
environment,
is_blocking,
module,
channel,
get_proj_name(project_id) project_name,
get_user_name(COALESCE(assignee_id, 0)) assignee_name,
get_user_name(COALESCE(created_by, 0)) created_by,
created_date,
get_user_name(COALESCE(modified_by, 0)) modified_by,
modified_date,
get_user_name(COALESCE(reported_by, 0)) reported_by,
reported_date,
row_hash
FROM ttickets
WHERE TICKET_NO=%s )
SELECT json_agg(t)
FROM t;
"""
values=(self.ticket_no,)
# print(f'Select : {query_sql} Values :{values}')
self.logger.debug(f'Select : {query_sql} Values :{values}')
try:
cursor=self.jdb.dbConn.cursor()
cursor.execute(query_sql,values)
json_data=cursor.fetchone()[0]
row_count=cursor.rowcount
print(f'Select Success with {row_count} row(s) Ticket ID {json_data}')
self.logger.debug(f'Select Success with {row_count} row(s) Ticket ID {json_data}')
response_data['dbQryStatus']='Success'
response_data['dbQryResponse']=json_data
return response_data
except (Exception, psycopg2.OperationalError) as error:
if(self.jdb.dbConn):
print(f'Error While Creating Ticket :{error.pgcode} == {error.pgerror}')
print('-'*80)
raise
def update_ticket(self):
response_data={}
self.logger.debug("Inside Update Ticket update_tickets")
update_sql="""
UPDATE TTICKETS
SET summary=%s,
description=%s,
severity=COALESCE(%s,severity),
priority=COALESCE(%s,priority),
issue_status=COALESCE(%s,issue_status),
issue_type=COALESCE(%s,issue_type),
environment=COALESCE(%s,issue_type),
modified_by=%s,
modified_date=%s,
reported_by=COALESCE(get_user_id(%s),reported_by),
reported_date=%s,
project_id=get_proj_id(%s),
assignee_id=get_user_id(%s),
is_blocking=%s,
module=%s,
channel=%s
WHERE ticket_no=%s and row_hash=%s
"""
values=(self.summary,self.description,self.severity,self.priority,
self.issue_status,self.issue_type,self.environment,self.modified_by,
datetime.datetime.today(),self.reported_by,datetime.datetime.today(),
self.project_name,self.assignee_name,self.is_blocking,self.module,self.channel,
self.ticket_no,self.row_hash,)
self.logger.debug(f'Update : {update_sql} {values}')
try:
print('-'*80)
print(type(self.jdb.dbConn))
cursor=self.jdb.dbConn.cursor()
cursor.execute(update_sql,values)
response_data['dbQryResponse']={"ticketId":self.ticket_no,"rowCount":cursor.rowcount}
self.jdb.dbConn.commit()
if response_data['dbQryResponse']['rowCount'] == 0:
response_data['dbQryStatus']='FailureNoRowFound'
else:
response_data['dbQryStatus']='Success'
return response_data
except (Exception, psycopg2.Error) as error:
if(self.jdb.dbConn):
print(f'Error While Updating Ticket {error}')
raise
def clone_ticket(self):
response_data={}
new_ticket_no='Error'
self.logger.debug("Inside Update Ticket update_tickets")
insert_sql="""
INSERT INTO ttickets (ticket_no, SUMMARY, description, issue_status, issue_type,
severity, priority, environment, is_blocking, module,created_by,
created_date, reported_by, reported_date, project_id,channel,row_hash)
SELECT get_issue_no_by_proj(get_proj_name(project_id)),
SUMMARY,
description,
issue_status,
issue_type,
severity,
priority,
environment,
is_blocking,
module,
%s,
%s,
reported_by,
reported_date,
project_id,
channel,
row_hash
FROM ttickets
WHERE ticket_no=%s
returning ticket_no;
"""
values=(self.created_by,datetime.datetime.today(),self.ticket_no,)
self.logger.debug(f'Update : {insert_sql} {values}')
try:
print('-'*80)
print(type(self.jdb.dbConn))
cursor=self.jdb.dbConn.cursor()
cursor.execute(insert_sql,values)
new_ticket_no=cursor.fetchone()[0]
self.jdb.dbConn.commit()
response_data['dbQryStatus']='Success'
response_data['dbQryResponse']={"clonedTicketNo":new_ticket_no,"rowCount":1}
return response_data
except (Exception, psycopg2.Error) as error:
if(self.jdb.dbConn):
print(f'Error While clone_ticket Ticket {error}')
raise
def update_ticket_assignee(self):
response_data={}
self.logger.debug("Inside update_ticket_assignee")
update_sql="""
UPDATE TTICKETS
SET assignee_id=%s,
modified_by=%s,
modified_date=%s
WHERE ticket_no=%s;
"""
values=(self.assignee_id,self.modified_by,self.modified_date,self.ticket_no,)
self.logger.debug(f'Update : {update_sql} {values}')
try:
print('-'*80)
print(type(self.jdb.dbConn))
cursor=self.jdb.dbConn.cursor()
cursor.execute(update_sql,values)
self.jdb.dbConn.commit()
response_data['dbQryStatus']='Success'
response_data['dbQryResponse']={"taskNo":self.ticket_no,"rowCount":1}
return response_data
except (Exception, psycopg2.Error) as error:
if(self.jdb.dbConn):
print(f'Error While update_ticket_assignee {error}')
raise
class JirigoChatBot(object):
def __init__(self, data=None):
self.jdb = JirigoDBConn()
self.logger = Logger()
self.query = ""
self.project_abbr = data.get('project_abbr', None)
self.chatObj = Chat(pairs, reflections)
self.lemmer = WordNetLemmatizer()
self.punctuations = dict(
(ord(punctuation), None) for punctuation in string.punctuation)
self.tag_map = defaultdict(lambda: wn.NOUN)
self.tag_map['J'] = wn.ADJ
self.tag_map['V'] = wn.VERB
self.tag_map['R'] = wn.ADV
def perform_lemmatization(self, tokens):
return [self.lemmer.lemmatize(token) for token in tokens]
def get_processed_text(self, document):
return perform_lemmatization(
nltk.word_tokenize(
document.lower().translate(punctuation_removal)))
def get_query_response(self, query):
response_data = {}
self.query = query
item_no = ""
chatbot_res = ""
print('*' * 40)
print(query)
print(sent_tokenize(query))
print(word_tokenize(query))
print(string.punctuation)
word_tokens = word_tokenize(query)
lmtzr = WordNetLemmatizer()
# print(self.perform_lemmatization(pos_tag(word_tokenize(query))))
print(pos_tag(word_tokenize(query)))
print('*' * 40)
print(self.project_abbr)
if (re.match(r'^GET_ITEM_DETAILS',
self.chatObj.respond(self.query),
flags=re.IGNORECASE)):
for token, tag in pos_tag(word_tokens):
lemma = lmtzr.lemmatize(token, self.tag_map[tag[0]])
print(f'{self.project_abbr}:{lemma}')
if (re.search(self.project_abbr, lemma, re.IGNORECASE)):
self.item_no = lemma
break
chatbot_res = self.get_item_status()
else:
chatbot_res = self.chatObj.respond(self.query)
response_data['dbQryStatus'] = 'Success'
response_data['dbQryResponse'] = chatbot_res
print(response_data)
return response_data
def bot_converse(self):
return self.chatObj.converse()
def get_canned_response(self):
return self.chatObj.respond(self.query)
def get_item_status(self):
response_data = {}
self.logger.debug(" Inside get_item_status")
print("Inside get_item_status")
print(self.item_no)
query_sql = """
SELECT item_no||' '||summary||' is in '||issue_status||' status' as item_status
FROM v_all_tickets_tasks vatt
WHERE lower(item_no)=lower(%s)
"""
values = (self.item_no, )
self.logger.debug(f'Select : {query_sql} values {values}')
try:
print('-' * 80)
cursor = self.jdb.dbConn.cursor()
cursor.execute(query_sql, values)
data = cursor.fetchone()
if data is None:
print('data is none')
data = f'Sorry, {self.item_no} doesn\'t exists'
row_count = cursor.rowcount
self.logger.debug(
f'Select Success with {row_count} row(s) get_item_status {data}'
)
return data[0]
except (Exception, psycopg2.Error) as error:
if (self.jdb.dbConn):
print(f'Error While get_item_status {error}')
raise
class JirigoTask(object):
def __init__(self, data={}):
print("Initializing JirigoTask")
print(f'In for Create/Update Task ****')
pprint(data)
self.task_int_id = data.get('task_int_id')
self.jdb = JirigoDBConn()
self.summary = data.get('summary', '')
self.description = data.get('description', '')
self.severity = data.get('severity', '')
self.priority = data.get('priority', '')
self.issue_type = data.get('issue_type', '')
self.issue_status = data.get('issue_status', '')
self.is_blocking = data.get('is_blocking', 'N')
self.environment = data.get('environment', '')
self.created_by = data.get('created_by', '')
self.created_date = datetime.datetime.now()
self.modified_by = data.get('modified_by', '')
self.modified_date = datetime.datetime.now()
self.reported_by = data.get('reported_by')
self.reported_date = data.get('reported_date', datetime.datetime.now())
self.task_no = data.get('task_no', '-')
self.project_name = data.get('project_name', '')
self.project_id = data.get('project_id', '')
self.assignee_name = data.get('assignee_name', '')
self.module_name = data.get('module_name', '')
self.estimated_time = 0 # At the time of task creation est is 0
self.start_date = data.get('start_date', None)
self.end_date = data.get('end_date', None)
self.row_hash = data.get('row_hash', None)
self.assignee_id = data.get('assignee_id', None)
self.task_estimates = data.get('task_estimates', None)
self.logger = Logger()
def create_task(self):
response_data = {}
self.logger.debug("Inside Create Task")
insert_sql = """ INSERT INTO
TTASKS( task_no,summary,description,severity,priority,
issue_status,issue_type,environment,is_blocking,created_by,
created_date,reported_by,reported_date,assignee_id,project_id,module_name,
estimated_time,start_date,end_date)
VALUES (get_issue_no_by_proj(%s),%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,
get_user_id(%s),%s,get_user_id(%s),get_proj_id(%s),%s,%s,%s,%s) returning task_no;
"""
values = (self.project_name, self.summary, self.description,
self.severity, self.priority, "Open", self.issue_type,
self.environment, self.is_blocking, self.created_by,
datetime.datetime.today(), self.reported_by,
datetime.datetime.today(), self.assignee_name,
self.project_name, self.module_name, self.estimated_time,
self.start_date, self.end_date)
self.logger.debug(f'Insert : {insert_sql} {values}')
try:
print('-' * 80)
print(type(self.jdb.dbConn))
cursor = self.jdb.dbConn.cursor()
cursor.execute(insert_sql, values)
task_no = cursor.fetchone()[0]
self.jdb.dbConn.commit()
row_count = cursor.rowcount
self.logger.debug(
f'Insert Success with {row_count} row(s) Task ID {task_no}')
response_data['dbQryStatus'] = 'Success'
response_data['dbQryResponse'] = {
"taskNo": task_no,
"rowCount": row_count
}
return response_data
except (Exception, psycopg2.Error) as error:
if (self.jdb.dbConn):
print(f'Error While Creating Task {error}')
raise
def get_all_tasks(self):
response_data = {}
self.logger.debug("Inside get_all_tasks")
query_sql = """
WITH t AS (
SELECT task_int_id,
task_no,
summary,
description,
issue_status,
issue_type,
severity,
priority,
environment,
is_blocking,
module_name,
get_user_name(created_by) created_by,
to_char(created_date, 'DD-Mon-YYYY HH24:MI:SS') created_date,
get_user_name(modified_by) modified_by,
to_char(created_date, 'DD-Mon-YYYY HH24:MI:SS') modified_date,
get_user_name(reported_by) reported_by,
to_char(created_date, 'DD-Mon-YYYY HH24:MI:SS') reported_date,
estimated_time/60 estimated_time,
get_user_name(assignee_id) assigned_to,
to_char(start_date, 'DD-Mon-YYYY') start_date,
to_char(end_date, 'DD-Mon-YYYY') end_date,
get_task_remaining_time(task_no) task_remaining_time
FROM ttasks
WHERE
project_id=COALESCE(%s,project_id) AND
(
created_by=COALESCE(%s,created_by) AND
COALESCE(assignee_id,-1)=COALESCE(%s,COALESCE(assignee_id,-1)) AND
COALESCE(modified_by,-1)=COALESCE(%s,COALESCE(modified_by,-1))
)
order by task_int_id
)
SELECT json_agg(t) from t;
"""
self.project_id = None if self.project_id == '' else self.project_id
self.assignee_id = None if self.assignee_id == '' else self.assignee_id
self.created_by = None if self.created_by == '' else self.created_by
self.modified_by = None if self.modified_by == '' else self.modified_by
values = (
self.project_id,
self.created_by,
self.assignee_id,
self.modified_by,
)
self.logger.debug(f'Select : {query_sql} values {values}')
try:
print('-' * 80)
cursor = self.jdb.dbConn.cursor()
cursor.execute(query_sql, values)
json_data = cursor.fetchone()[0]
row_count = cursor.rowcount
self.logger.debug(
f'Select Success with {row_count} row(s) Task ID {json_data}')
response_data['dbQryStatus'] = 'Success'
response_data['dbQryResponse'] = json_data
return response_data
except (Exception, psycopg2.Error) as error:
if (self.jdb.dbConn):
print(f'Error While Creating Task {error}')
raise
def get_task_details(self):
self.logger.debug('Inside get_task_details')
response_data = {}
query_sql = """
WITH t AS
(SELECT task_int_id,
task_no,
SUMMARY,
description,
issue_status,
issue_type,
severity,
priority,
environment,
is_blocking,
module_name,
get_proj_name(project_id) project_name,
get_user_name(COALESCE(assignee_id, 0)) assignee_name,
get_user_name(COALESCE(created_by, 0)) created_by,
created_date,
get_user_name(COALESCE(modified_by, 0)) modified_by,
modified_date,
get_user_name(COALESCE(reported_by, 0)) reported_by,
reported_date,
estimated_time/60 estimated_time,
start_date,
end_date,
get_task_remaining_time(task_no) task_remaining_time,
row_hash,
get_sprint_name_for_task(task_no) as sprint_name
FROM ttasks
WHERE TASK_NO=%s )
SELECT json_agg(t)
FROM t;
"""
values = (self.task_no, )
# print(f'Select : {query_sql} Values :{values}')
self.logger.debug(f'Select : {query_sql} Values :{values}')
try:
cursor = self.jdb.dbConn.cursor()
cursor.execute(query_sql, values)
json_data = cursor.fetchone()[0]
row_count = cursor.rowcount
print(
f'Select Success with {row_count} row(s) Task ID {json_data}')
self.logger.debug(
f'Select Success with {row_count} row(s) Task ID {json_data}')
response_data['dbQryStatus'] = 'Success'
response_data['dbQryResponse'] = json_data
return response_data
except (Exception, psycopg2.OperationalError) as error:
if (self.jdb.dbConn):
print(
f'Error While Creating Task :{error.pgcode} == {error.pgerror}'
)
print('-' * 80)
raise
def update_task(self):
response_data = {}
self.logger.debug("Inside Update Task update_tasks")
update_sql = """
UPDATE TTASKS
SET summary=%s,
description=%s,
severity=COALESCE(%s,severity),
priority=COALESCE(%s,priority),
issue_status=COALESCE(%s,issue_status),
issue_type=COALESCE(%s,issue_type),
environment=COALESCE(%s,issue_type),
modified_by=%s,
modified_date=%s,
reported_by=COALESCE(get_user_id(%s),reported_by),
reported_date=%s,
project_id=get_proj_id(%s),
assignee_id=get_user_id(%s),
is_blocking=%s,
module_name=%s,
start_date=%s,
end_date=%s
WHERE task_no=%s and row_hash=%s;
"""
self.estimated_time = None if self.estimated_time == '' else self.estimated_time
values = (
self.summary,
self.description,
self.severity,
self.priority,
self.issue_status,
self.issue_type,
self.environment,
self.modified_by,
datetime.datetime.today(),
self.reported_by,
datetime.datetime.today(),
self.project_name,
self.assignee_name,
self.is_blocking,
self.module_name,
self.start_date,
self.end_date,
self.task_no,
self.row_hash,
)
self.logger.debug(f'Update : {update_sql} {values}')
try:
print('-' * 80)
cursor = self.jdb.dbConn.cursor()
cursor.execute(update_sql, values)
print(dir(cursor))
response_data['dbQryResponse'] = {
"taskNo": self.task_no,
"rowCount": cursor.rowcount
}
print(response_data)
print('-' * 80)
self.jdb.dbConn.commit()
if response_data['dbQryResponse']['rowCount'] == 0:
response_data['dbQryStatus'] = 'FailureNoRowFound'
else:
response_data['dbQryStatus'] = 'Success'
return response_data
except (Exception, psycopg2.Error) as error:
if (self.jdb.dbConn):
print(f'Error While Updating Task {error}')
raise
def clone_task(self):
response_data = {}
new_task_no = 'Error'
self.logger.debug("Inside Update Task update_tasks")
insert_sql = """
INSERT INTO ttasks (task_no, SUMMARY, description, issue_status, issue_type,
severity, priority, environment, is_blocking, module_name,created_by,
created_date, reported_by, reported_date, project_id,estimated_time,
start_date,end_date,row_hash)
SELECT get_issue_no_by_proj(get_proj_name(project_id)),
SUMMARY,
description,
issue_status,
issue_type,
severity,
priority,
environment,
is_blocking,
module_name,
%s,
%s,
reported_by,
reported_date,
project_id,
estimated_time,
start_date,
end_date,
row_hash
FROM ttasks
WHERE task_no=%s
returning task_no;
"""
values = (
self.created_by,
datetime.datetime.today(),
self.task_no,
)
self.logger.debug(f'Update : {insert_sql} {values}')
try:
print('-' * 80)
print(type(self.jdb.dbConn))
cursor = self.jdb.dbConn.cursor()
cursor.execute(insert_sql, values)
new_task_no = cursor.fetchone()[0]
self.jdb.dbConn.commit()
response_data['dbQryStatus'] = 'Success'
response_data['dbQryResponse'] = {
"clonedTaskNo": new_task_no,
"rowCount": 1
}
return response_data
except (Exception, psycopg2.Error) as error:
if (self.jdb.dbConn):
print(f'Error While clone_task Task {error}')
raise
def update_task_assignee(self):
response_data = {}
self.logger.debug("Inside update_task_assignee")
update_sql = """
UPDATE TTASKS
SET assignee_id=%s,
modified_by=%s,
modified_date=%s
WHERE task_no=%s;
"""
values = (
self.assignee_id,
self.modified_by,
self.modified_date,
self.task_no,
)
self.logger.debug(f'Update : {update_sql} {values}')
try:
print('-' * 80)
print(type(self.jdb.dbConn))
cursor = self.jdb.dbConn.cursor()
cursor.execute(update_sql, values)
self.jdb.dbConn.commit()
response_data['dbQryStatus'] = 'Success'
response_data['dbQryResponse'] = {
"taskNo": self.task_no,
"rowCount": 1
}
return response_data
except (Exception, psycopg2.Error) as error:
if (self.jdb.dbConn):
print(f'Error While update_task_assignee {error}')
raise
def update_task_status(self):
response_data = {}
self.logger.debug("Inside update_task_status")
update_sql = """
UPDATE TTASKS
SET issue_status=%s,
modified_by=%s,
modified_date=%s
WHERE task_no=%s;
"""
values = (
self.issue_status,
self.modified_by,
self.modified_date,
self.task_no,
)
self.logger.debug(f'Update : {update_sql} {values}')
try:
print('-' * 80)
print(type(self.jdb.dbConn))
cursor = self.jdb.dbConn.cursor()
cursor.execute(update_sql, values)
self.jdb.dbConn.commit()
response_data['dbQryStatus'] = 'Success'
response_data['dbQryResponse'] = {
"taskNo": self.task_no,
"rowCount": 1
}
return response_data
except (Exception, psycopg2.Error) as error:
if (self.jdb.dbConn):
print(f'Error While update_task_status {error}')
raise
def create_update_task_estimates(self):
response_data = {}
t_task_estimated_time = 0
self.logger.debug("create_update_task_estimates")
set_task_estimate_to_zero = """
UPDATE ttasks
SET estimated_time=0
WHERE task_no=%s
"""
t_task_no = (self.task_no, )
clean_up_task_estimates_sql = """
DELETE
FROM ttask_estimates
WHERE task_no=%s
"""
t_task_no = (self.task_no, )
print(f'task No {t_task_no}')
self.logger.debug(f'task No {t_task_no}')
ins_task_estimate_sql = """
INSERT INTO ttask_estimates (task_no,activity,estimated_time)
VALUES %s
"""
get_task_estimate_sql = """
SELECT estimated_time/60 estimated_time
FROM ttasks
WHERE task_no=%s
"""
try:
print('-' * 80)
cursor = self.jdb.dbConn.cursor()
cursor.execute(set_task_estimate_to_zero, t_task_no)
row_count = cursor.rowcount
print(f'Task estimate set to zero OK {row_count}')
self.logger.debug(f'Task estimate set to zero OK {row_count}')
cursor.execute(clean_up_task_estimates_sql, t_task_no)
row_count = cursor.rowcount
print(f'Delete Success with {row_count} row(s) ID {self.task_no}')
self.logger.debug(
f'Delete Success with {row_count} row(s) ID {self.task_no}')
print(self.task_estimates)
estimate_values = [(self.task_no, x['activity'],
x['estimated_time'])
for x in self.task_estimates]
print(f'estimate_values {estimate_values}')
execute_values(cursor,
ins_task_estimate_sql,
estimate_values,
template="(%s,%s,%s)")
row_count = cursor.rowcount
self.logger.debug(
f'Bulk Insert Success with {row_count} row(s) ID {self.task_no}'
)
self.jdb.dbConn.commit()
cursor.execute(get_task_estimate_sql, t_task_no)
row_count = cursor.rowcount
t_task_estimated_time = cursor.fetchone()[0]
response_data['dbQryStatus'] = 'Success'
response_data['dbQryResponse'] = {
"rowCount": row_count,
"taskEstimatedTime": t_task_estimated_time
}
return response_data
except (Exception, psycopg2.Error) as error:
if (self.jdb.dbConn):
print(f'Error While creating estimates {error}')
raise
def get_task_estimates(self):
self.logger.debug('Inside get_task_estimates')
response_data = {}
query_sql = """
WITH t AS
(SELECT *
FROM ttask_estimates
WHERE task_no=%s )
SELECT json_agg(t)
FROM t;
"""
values = (self.task_no, )
# print(f'Select : {query_sql} Values :{values}')
self.logger.debug(f'Select : {query_sql} Values :{values}')
try:
cursor = self.jdb.dbConn.cursor()
cursor.execute(query_sql, values)
json_data = cursor.fetchone()[0]
row_count = cursor.rowcount
print(
f'Select Success with {row_count} row(s) Task ID {json_data}')
self.logger.debug(
f'Select Success with {row_count} row(s) Task ID {json_data}')
response_data['dbQryStatus'] = 'Success'
response_data['dbQryResponse'] = json_data
return response_data
except (Exception, psycopg2.OperationalError) as error:
if (self.jdb.dbConn):
print(
f'Error While Creating Task :{error.pgcode} == {error.pgerror}'
)
print('-' * 80)
raise