def new_project(self):
try:
conn = mysqlconnection(HOST, USERNAME, PASSWORD, DATABASE)
status_code = 'PRST00001'
condNewProject = 'status_id = %s'
getNewProject = conn.select(
'v_project_detail',
condNewProject,
'project_id, client_name, project_name, pid, startDate, endDate, mandays, status_name',
status_id=status_code)
detNewProject = []
for index, list in enumerate(getNewProject):
i = {
'project_id': list[0],
'client_name': list[1],
'project_name': list[2],
'pid': list[3],
'startDate': list[4],
'endDate': list[5],
'mandays': list[6],
'status_name': list[7]
}
detNewProject.append(i)
return detNewProject
except Exception as e:
return "Error Database: %s" % str(e)
def detail(self, empid):
self.empid = empid
try:
conn = mysqlconnection(HOST, USERNAME, PASSWORD, DATABASE)
condEmp = 'employee_id = %s'
getEmp = conn.select('v_employees_detail', condEmp, 'employee_id, NIK, email, firstname, lastname, birthday, gender, address, city_name, phone, division_name, title_name, join_date', employee_id=self.empid)
detEmp = []
for index, list in enumerate(getEmp):
i = {
'employee_id': list[0],
'NIK': list[1],
'email': list[2],
'firstname': list[3],
'lastname': list[4],
'birtday': list[5],
'gender': list[6],
'address': list[7],
'city_name': list[8],
'phone': list[9],
'division_name': list[10],
'title_name': list[11],
'join_date': list[12],
}
detEmp.append(i)
return detEmp
except Exception as e:
return "Error Database: %s" % str(e)
def getAllTeamProject(self):
try:
conn = mysqlconnection(HOST, USERNAME, PASSWORD, DATABASE)
cond = 'status = %s'
detail = conn.select(
'v_employee_team_project',
cond,
'employee_id, firstname, lastname, email, title_name, initial, assign_project_user, project_id',
status='added')
getDetail = []
for index, list in enumerate(detail):
i = {
'employee_id': list[0],
'firstname': list[1],
'lastname': list[2],
'email': list[3],
'title_name': list[4],
'initial': list[5],
'assign_project_user': list[6],
'project_id': list[7]
}
getDetail.append(i)
return getDetail
except Exception as e:
return "Error Database: %s" % str(e)
def getDetailUpdateTask(self, taskid):
try:
conn = mysqlconnection(HOST, USERNAME, PASSWORD, DATABASE)
cond = 'task_id = %s'
getData = conn.select('v_detail_update_task',
cond,
'*',
task_id=taskid)
detail = []
for index, list in enumerate(getData):
start_date = datetime.strptime(str(list[4]),
'%Y-%m-%d').strftime('%d/%m/%Y')
end_date = datetime.strptime(str(list[5]),
'%Y-%m-%d').strftime('%d/%m/%Y')
i = {
'task_id': list[0],
'project_id': list[1],
'name': list[2],
'pid': list[3],
'Task_StartDate': start_date,
'Task_EndDate': end_date,
'master_task_id': list[6],
'task_name': list[7],
'task_description': list[8]
}
detail.append(i)
return detail
except Exception as e:
return "Error Database: %s" % str(e)
def getDetailProblem(self, problemid):
try:
conn = mysqlconnection(HOST, USERNAME, PASSWORD, DATABASE)
cond = 'problem_log_id = %s'
getData = conn.select('v_problemlog_detail', cond, '*', problem_log_id=problemid)
detail = []
for index, list in enumerate(getData):
date_start = datetime.strptime(str(list[7]), '%Y-%m-%d').strftime('%d/%m/%Y')
date_end = datetime.strptime(str(list[8]), '%Y-%m-%d').strftime('%d/%m/%Y')
date_target = datetime.strptime(str(list[9]), '%Y-%m-%d').strftime('%d/%m/%Y')
i = {
'problem_log_id': list[0],
'project_id': list[1],
'project_name': list[2],
'client_id': list[3],
'client_name': list[4],
'pid': list[5],
'problem_name': list[6],
'start_date': date_start,
'end_date': date_end,
'target_date': date_target,
'master_problem_id': list[10],
'status_problem': list[11],
'description': list[12],
'increment_process': list[13],
'problem_note': list[14]
}
detail.append(i)
return detail
except Exception as e:
return "Error Database: %s" % str(e)
def homeClient(self):
try:
conn = mysqlconnection(HOST, USERNAME, PASSWORD, DATABASE)
getClient = conn.execquery('select * from client')
return getClient
except Exception as e:
return "Error Database: %s" % str(e)
def sendingCloseTask(self, taskid):
try:
conn = mysqlconnection(HOST, USERNAME, PASSWORD, DATABASE)
cond = 'task_id = %s'
getEmail = conn.select('v_email_send_task',
cond,
'email, firstname, lastname, name, pid',
task_id=taskid)
start = 0
while (start < len(getEmail)):
email = getEmail[start]['email']
name = getEmail[start]['firstname'] + " " + getEmail[start][
'lastname']
project_name = getEmail[start]['name']
pid = getEmail[start]['pid']
status = 'Task Success Close By Admin'
link = 'http://localhost:14045/dasboard'
from email_sending import send_email
mail = send_email(email)
send = mail.nofitication(name, pid, project_name, status, link)
start += 1
except Exception as e:
return "Error Database: %s" % str(e)
def addAssignProblemEmp(self, problemid, assign, unassign):
try:
conn = mysqlconnection(HOST, USERNAME, PASSWORD, DATABASE)
validate = []
data = json.loads(assign)
data_unassign = json.loads(unassign)
start = 0
# for assign new task
while start < len(data):
getRowExist = conn.execquery("select * from problem_assign where problem_log_id ='"+problemid+"' and teamproject_id = '" +data[start]+"'")
if getRowExist == 0:
# insert assign user
addNewAssign = conn.insert('problem_assign', problem_log_id=problemid, teamproject_id=data[start])
if addNewAssign == 0:
validate.append('success')
else:
validate.append('failed')
start += 1
# for unassign user
count = 0
while count < len(data_unassign):
print data_unassign[count]
getAssignExist = conn.execquery("select * from problem_assign where problem_log_id ='"+problemid+"' and teamproject_id='"+data_unassign[count]+"'")
if getAssignExist == 1:
delAssign = conn.execquery("delete from problem_assign where problem_log_id = '"+problemid+"' and teamproject_id='"+data_unassign[count]+"'")
if delAssign == 1:
validate.append('success')
else:
validate.append('failed')
count += 1
return validate
except Exception as e:
return "Error Database: %s" % str(e)
def detail(self, clientid):
self.clientid = clientid
try:
conn = mysqlconnection(HOST, USERNAME, PASSWORD, DATABASE)
condClient = 'client_id = %s'
getClient = conn.select('v_client_detail',
condClient,
'*',
client_id=self.clientid)
detClient = []
for index, list in enumerate(getClient):
i = {
'client_id': list[0],
'name': list[1],
'email': list[2],
'address': list[3],
'city_code': list[4],
'city_name': list[5],
'pic_name': list[6],
'pic_number': list[7],
'pic_email': list[8],
'client_code': list[9]
}
detClient.append(i)
return detClient
except Exception as e:
return "Error Database: %s" % str(e)
def homeTask(self):
try:
conn = mysqlconnection(HOST, USERNAME, PASSWORD, DATABASE)
getTask = conn.execquery('select * from task')
return getTask
except Exception as e:
return "Error Database: %s" % str(e)
def homeEmployee(self):
try:
conn = mysqlconnection(HOST, USERNAME, PASSWORD, DATABASE)
getEmployee = conn.execquery('select * from employees')
return getEmployee
except Exception as e:
return "Error Database: %s" % str(e)
def getTaskOnGoing(self):
task_status = 'STAT00002'
try:
conn = mysqlconnection(HOST, USERNAME, PASSWORD, DATABASE)
cond = 'taskstatus_id = %s'
getData = conn.select('v_task_detail',
cond,
'*',
taskstatus_id=task_status)
detail = []
for index, list in enumerate(getData):
i = {
'task_id': list[0],
'project_id': list[1],
'pid': list[2],
'client_id': list[3],
'name_client': list[4],
'task_startdate': list[5],
'task_enddate': list[6],
'master_task_id': list[7],
'task_name': list[8],
'project_status_id': list[9],
'status_project': list[10],
'taskstatus_id': list[11],
'status_task': list[12]
}
detail.append(i)
return detail
except Exception as e:
return "Error Database: %s" % str(e)
def taskAdd(self, project_id, task_startDate, task_endDate, task_target,
task_id, task_description, incremental, member_split):
taskstatus = 'STAT00001'
try:
conn = mysqlconnection(HOST, USERNAME, PASSWORD, DATABASE)
addTask = conn.customquery("call sp_add_task('" + project_id +
"','" + task_startDate + "','" +
task_endDate + "','" + task_target +
"','" + task_id + "','" + taskstatus +
"','" + task_description + "','" +
incremental + "')")
if len(addTask) > 0:
start = 0
taskCode = addTask[0][0]
insertStatus = []
while (start < len(member_split)):
taskAssign = member_split[start]
addAssignTask = conn.insert('task_assign',
task_id=taskCode,
teamproject_id=taskAssign)
insertStatus.append(addAssignTask)
start += 1
if 0 in insertStatus:
return 0
else:
return 1
except Exception as e:
return "Error Database: %s" % str(e)
def projectApproveDet(self, project_id):
try:
conn = mysqlconnection(HOST, USERNAME, PASSWORD, DATABASE)
condProject = 'project_id = %s'
getDetProject = conn.select('v_project_detail',
condProject,
'*',
project_id=project_id)
detProject = []
for index, list in enumerate(getDetProject):
i = {
'project_id': list[0],
'client_id': list[1],
'client_name': list[2],
'project_name': list[3],
'mandays': list[4],
'startDate': list[5],
'endDate': list[6],
'status_id': list[7],
'status_name': list[8],
'pid': list[10],
'project_manager': list[11],
'description': list[16],
'doc_project': list[17],
'doc_size': list[18],
'priority': list[19]
}
detProject.append(i)
return detProject
except Exception as e:
return "Error Database: %s" % str(e)
def getDetailProject(self, projectid):
try:
conn = mysqlconnection(HOST, USERNAME, PASSWORD, DATABASE)
cond = 'project_id = %s'
getData = conn.select(
'project',
cond,
'project_id, client_id, name, mandays, startDate, endDate, pid, priority, employee_id, description',
project_id=projectid)
detail = []
for index, list in enumerate(getData):
date_start = datetime.strptime(str(list[4]),
'%Y-%m-%d').strftime('%d/%m/%Y')
date_end = datetime.strptime(str(list[5]),
'%Y-%m-%d').strftime('%d/%m/%Y')
i = {
'project_id': list[0],
'client_id': list[1],
'name': list[2],
'mandays': list[3],
'startDate': date_start,
'endDate': date_end,
'pid': list[6],
'priority': list[7],
'employee_id': list[8],
'description': list[9]
}
detail.append(i)
return detail
except Exception as e:
return "Error Database: %s" % str(e)
def addProject(self, client_id, name, mandays, startDate, endDate, pid,
priority, employee_id, description, doc_project, doc_size):
self.client_id = client_id
self.name = name
self.mandays = mandays
self.startDate = startDate
self.endDate = endDate
self.pid = pid
self.priority = priority
self.employee_id = employee_id
self.description = description
self.doc_project = doc_project
self.doc_size = doc_size
print self.startDate
try:
conn = mysqlconnection(HOST, USERNAME, PASSWORD, DATABASE)
projectAdd = conn.insert('project',
client_id=self.client_id,
name=self.name,
mandays=self.mandays,
startDate=self.startDate,
endDate=self.endDate,
pid=self.pid,
priority=self.priority,
employee_id=self.employee_id,
description=self.description,
doc_project=self.doc_project,
doc_size=self.doc_size)
print projectAdd
if projectAdd == 0:
return 0
else:
return 1
except Exception as e:
return "Error Database: %s" % str(e)
def decreaseTeamProject(self, project_id, unassign_user):
try:
conn = mysqlconnection(HOST, USERNAME, PASSWORD, DATABASE)
condProject = 'project_id = %s'
getListExisting = conn.select('v_employee_team_project',
condProject,
'employee_id, TeamProject_ID',
project_id=project_id)
# from assign to unassign
x = 0
while (x < len(getListExisting)):
if getListExisting[x][0] not in unassign_user:
decrease = conn.execquery(
'delete from team_project where project_id = "' +
project_id + '" and employee_id = "' +
getListExisting[x][0] + '"')
print decrease
if decrease > 0:
return 0
else:
return 1
x += 1
except Exception:
return 1
def addTeamProject(self, project_id, assign_user):
try:
conn = mysqlconnection(HOST, USERNAME, PASSWORD, DATABASE)
condProject = 'project_id = %s'
getListExisting = conn.select('v_employee_team_project',
condProject,
'employee_id, TeamProject_ID',
project_id=project_id)
# from unasign to assign
y = 0
while (y < len(assign_user)):
x = 0
userinSystem = []
while (x < len(getListExisting)):
userinSystem.append(getListExisting[x][0])
x += 1
if assign_user[y] not in userinSystem:
addMember = conn.insert('team_project',
project_id=project_id,
employee_id=assign_user[y])
if addMember == 0:
return 0
else:
return 1
y += 1
except Exception:
return 1
def getEmployeeDetail(self, empid):
try:
conn = mysqlconnection(HOST, USERNAME, PASSWORD, DATABASE)
cond = "employee_id = %s"
getData = conn.select('v_employee_detail_edit', cond, '*', employee_id=empid)
detailData = []
for index, list in enumerate(getData):
date_birthday = datetime.strptime(str(list[6]), '%Y-%m-%d').strftime('%d/%m/%Y')
date_join = datetime.strptime(str(list[14]), '%Y-%m-%d').strftime('%d/%m/%Y')
i = {
'employee_id': list[0],
'nik': list[1],
'email': list[2],
'password': list[3],
'firstname': list[4],
'lastname': list[5],
'birthday': date_birthday,
'gender': list[7],
'address': list[8],
'city_code': list[9],
'province_id': list[10],
'phone': list[11],
'division_id': list[12],
'emptitle_id': list[13],
'join_date': date_join
}
detailData.append(i)
return detailData
except Exception as e:
return "Error Database: %s" % str(e)
def listAllClient(self, clientid):
try:
conn = mysqlconnection(HOST, USERNAME, PASSWORD, DATABASE)
cond = 'client_id = %s'
getAllProject = conn.select(
'v_project_detail',
cond,
'left(project_name, 15), client_name, status_name, left(description, 40), enddate, firstname, lastname, project_id',
client_id=clientid)
detAllProject = []
for index, list in enumerate(getAllProject):
i = {
'project_name': list[0],
'client_name': list[1],
'status_name': list[2],
'desc': list[3],
'deadline': list[4],
'firstname': list[5],
'lastname': list[6],
'project_id': list[7]
}
detAllProject.append(i)
return detAllProject
except Exception as e:
return "Error Database: %s" % str(e)
def getDocDetailProject(self, docid):
try:
conn = mysqlconnection(HOST, USERNAME, PASSWORD, DATABASE)
cond = 'document_no = %s'
getData = conn.select('v_document_detail_project',
cond,
'*',
document_no=docid)
detail = []
for index, list in enumerate(getData):
i = {
'document_no': list[0],
'document_type_id': list[1],
'document_name': list[2],
'task_id': list[3],
'project_id': list[4],
'project_name': list[5],
'client_id': list[6],
'client_name': list[7],
'pid': list[8],
'doc_name': list[9],
'status': list[10]
}
detail.append(i)
return detail
except Exception as e:
return "Error Database: %s" % str(e)
def taskDetail(self, taskid):
try:
conn = mysqlconnection(HOST, USERNAME, PASSWORD, DATABASE)
cond = 'task_id = %s'
getData = conn.select('v_task_detail_view',
cond,
'*',
task_id=taskid)
detail = []
for index, list in enumerate(getData):
dateStart = list[11].strftime('%d/%m/%Y')
dateEnd = list[12].strftime('%d/%m/%Y')
i = {
'task_id': list[0],
'master_task_id': list[1],
'task_name': list[2],
'project_status_id': list[3],
'category_task': list[4],
'task_description': list[5],
'project_id': list[6],
'client_id': list[7],
'client_name': list[8],
'project_name': list[9],
'pid': list[10],
'task_startDate': dateStart,
'task_enddate': dateEnd,
'taskstatus_id': list[13],
'task_status': list[14],
'task_notes': list[15],
'task_proses_increment': list[16]
}
detail.append(i)
return detail
except Exception as e:
return "Error Database: %s" % str(e)
def homeTaskPending(self):
try:
conn = mysqlconnection(HOST, USERNAME, PASSWORD, DATABASE)
getTaskPending = conn.execquery(
'select * from task where taskstatus_id not in ("STAT00004")')
return getTaskPending
except Exception as e:
return "Error Database: %s" % str(e)
def check_name(self, nik):
try:
conn = mysqlconnection(HOST, USERNAME, PASSWORD, DATABASE)
cond = 'nik = %s'
getData = conn.select('employees', cond, 'firstname, lastname', nik=nik)
name_emp = getData[0][0]+" "+getData[0][1]
return name_emp
except Exception as e:
return "Error Database: %s" % str(e)
def add(self, nik, email, password, firstname, lastname, birthday, gender, address, city_code, phone, division_id, empTitle_id, join_date):
try:
conn = mysqlconnection(HOST, USERNAME, PASSWORD, DATABASE)
addEmp = conn.insert('employees', nik=nik, email=email, password=password, firstname=firstname, lastname=lastname, birthday=birthday, gender=gender, address=address, city_code=city_code, phone=phone, division_id=division_id, empTitle_id=empTitle_id, join_date=join_date)
if addEmp == 0:
return True
else:
return False
except Exception as e:
return "Error Database: %s" % str(e)
def uploadDocument(self, doc_name, problemid, doc_code, doc_size, doc_url, doc_extention):
try:
conn = mysqlconnection(HOST, USERNAME, PASSWORD, DATABASE)
addDoc = conn.insert('document_problemlog', document_filename=doc_name, document_type_problemlog_id=doc_code, document_url=doc_url, problem_log_id=problemid, document_size=doc_size, document_extention=doc_extention)
if addDoc == 0:
return True
else:
return False
except Exception as e:
return "Error Database: %s" % str(e)
def checkEmp(self):
try:
conn = mysqlconnection(HOST, USERNAME, PASSWORD, DATABASE)
getAll = conn.select('employees', None, 'employee_id')
if len(getAll) > 0:
return len(getAll)
else:
return False
except Exception as e:
return "Error Database: %s" % str(e)
def taskNote(self, taskid, note):
try:
conn = mysqlconnection(HOST, USERNAME, PASSWORD, DATABASE)
cond = 'task_id = %s'
updData = conn.update('task', cond, taskid, task_notes=note)
if updData:
return True
else:
return False
except Exception as e:
return "Error Database: %s" % str(e)
def getClient(self):
try:
conn = mysqlconnection(HOST, USERNAME, PASSWORD, DATABASE)
clientAll = conn.select('client', None, 'client_id, name')
detClient = []
for index, list in enumerate(clientAll):
i = {'client_id': list[0], 'client_name': list[1]}
detClient.append(i)
return detClient
except Exception as e:
return "Error Database: %s" % str(e)
def documentUpdateApprove(self, doc_id, status):
try:
conn = mysqlconnection(HOST, USERNAME, PASSWORD, DATABASE)
cond = 'document_no = %s'
updData = conn.update('document', cond, doc_id, status=status)
if updData:
return True
else:
return False
except Exception as e:
"Error Database: %s" % str(e)
