def get(self):
ACTION = self.GET.get('ACTION', '')
if ACTION == 'ONE':
pk = self.GET.get('id')
if pk:
model = self.db.query(User).filter(User.id == pk).first()
if model:
return JsonResponse(self, '000', data=model.json)
else:
return JsonResponse(self, '001', msg="你查询的记录不存在！")
else:
return JsonResponse(self, '100', msg="请传入参数id！")
elif ACTION == 'QUERY':
query = '''
select u.id,
u.username, -- 用户名
u.fullname, -- 姓名
u.email, -- 邮箱
u.mobile, -- 手机号
r.name as rolename, -- 角色名称
u.entry_date -- 添加时间
from secu_user as u
left join secu_role as r on u.roles = r.id
order by u.fullname;
'''
data = json_by_result(self.db.execute(query).fetchall())
return JsonResponse(self, '000', data=data)
elif ACTION == 'SELF':
pk = self.session['userid']
query = '''
select
u.fullname,
u.roles,
r.rights
from secu_user u
left join secu_role r on u.roles = r.id
where u.id = :id;
'''
data = json_by_result(
self.db.execute(query, {
'id': pk
}).fetchall())
return JsonResponse(self, '000', data=data)
else:
return JsonResponse(self, '100', msg="缺失参数ACTION")
def test_by_correct_info(self):
response = requests.delete(self.url, json={"id": self.data['id']})
resp = response.json()
self.assertEqual('000', resp['code'])
query = '''
select * from todo_item;
'''
result = json_by_result(self.db.execute(query).fetchall())
self.assertEqual(0, len(result))
query = '''
select * from todo_person_in_charge;
'''
result = json_by_result(self.db.execute(query).fetchall())
self.assertEqual(0, len(result))
def get(self):
ACTION = self.GET.get('ACTION', '')
if ACTION == 'ONE':
pk = self.GET.get('id')
if pk:
model = self.db.query(Project).filter(Project.id == pk).first()
if model:
return JsonResponse(self, '000', data=model.json)
else:
return JsonResponse(self, '001', msg="你查询的记录不存在！")
else:
return JsonResponse(self, '100', msg="请传入参数id！")
elif ACTION == 'QUERY':
query = '''
select p.id,
p.name,
p.desp,
p.cats,
p.statuses,
p.priorities,
p.entry_date,
u.fullname
from todo_project as p
left join secu_user as u on p.entry_user = u.id
order by p.identity desc;
'''
data = json_by_result(self.db.execute(query).fetchall())
return JsonResponse(self, '000', data=data)
elif ACTION == 'OPTIONS':
return JsonResponse(self, '000', data=Project.options)
else:
return JsonResponse(self, '100', msg="缺失参数ACTION")
def get(self):
ACTION = self.GET.get('ACTION')
if ACTION == 'ONE':
pk = self.GET.get('id')
if pk:
model = self.db.query(Role).filter(Role.id == pk).first()
if model:
return JsonResponse(self, '000', data=model.json)
else:
return JsonResponse(self, '001', msg="你查询的记录不存在！")
else:
return JsonResponse(self, '100', msg="请传入参数id！")
elif ACTION == 'RIGHTS':
pk = self.GET.get('id')
if pk:
model = self.db.query(Role).filter(Role.id == pk).first()
rights = model.rights if model else ''
query = '''
select id,
name,
description,
case when locate(id, :rights) > 0 then 1 else 0 end as ishave
from secu_right order by id;
'''
result = json_by_result(self.db.execute(query, {'id': pk, 'rights': rights}).fetchall())
return JsonResponse(self, '000', data=result)
else:
return JsonResponse(self, '100', msg="请传入参数id！")
elif ACTION == 'QUERY':
result = self.db.query(Role).all()
return JsonResponse(self, '000', data=[r.json for r in result])
else:
return JsonResponse(self, '100', msg="缺失参数ACTION")
def test_by_correct_info(self):
response = requests.post(self.url, json=self.data)
resp = response.json()
self.assertEqual('000', resp['code'])
query = '''
select * from todo_work where itemid=:itemid;
'''
result = json_by_result(self.db.execute(query, self.data).fetchall())
self.assertEqual(1, len(result))
def test_by_invalid_duration(self):
self.data['duration'] = 500
response = requests.post(self.url, json=self.data)
resp = response.json()
self.assertEqual('100', resp['code'])
query = '''
select * from todo_work where itemid=:itemid;
'''
result = json_by_result(self.db.execute(query, self.data).fetchall())
self.assertEqual(0, len(result))
def test_by_no_exist_id(self):
response = requests.delete(
self.url, json={"id": str(uuid.uuid4()).replace('-', '')})
resp = response.json()
self.assertEqual('001', resp['code'])
query = '''
select * from todo_work where itemid=:itemid;
'''
result = json_by_result(self.db.execute(query, self.data).fetchall())
self.db.commit()
self.assertEqual(1, len(result))
def get(self):
ACTION = self.GET.get('ACTION', '')
if ACTION == 'GET-GROUP-USERS':
query = '''
select pg.identity,
pg.projectid,
pg.userid,
u.fullname
from todo_project_group as pg
left join secu_user as u on pg.userid = u.id
where projectid = :projectid;
'''
result = json_by_result(
self.db.execute(query, {
'projectid': self.GETPOST['projectid']
}).fetchall())
return JsonResponse(self, '000', data=result)
else:
return JsonResponse(self, '100', msg="缺失参数ACTION")
def test_by_correct_info(self):
self.data["title"] = "新的任务名称"
self.data["person_in_charges"] = [{
"itemid":
"337b70d58cf841759ca0469fe7109a2e",
"itemstatus":
"需求评审",
"person_in_charge":
"556a41ea4f48432d84a8e973bc03c3ec"
}, {
"itemid":
"337b70d58cf841759ca0469fe7109a2e",
"itemstatus":
"代码开发",
"person_in_charge":
"68AB8F2982D04D8AA75EF64A52E3301E"
}, {
"itemid":
"337b70d58cf841759ca0469fe7109a2e",
"itemstatus":
"代码评审",
"person_in_charge":
"9b06a4029b7a4fb1869b71a44c32e8b2"
}]
response = requests.put(self.url, json=self.data)
resp = response.json()
self.assertEqual('000', resp['code'])
query = '''
select title from todo_item;
'''
result = self.db.execute(query).scalar()
self.assertEqual('新的任务名称', result)
query = '''
select * from todo_person_in_charge;
'''
result = json_by_result(self.db.execute(query).fetchall())
self.assertEqual(3, len(result))
def get(self):
ACTION = self.GET.get('ACTION', '')
if ACTION == 'ONE':
pk = self.GET.get('id')
if pk:
model = self.db.query(Work).filter(Work.id == pk).first()
if model:
return JsonResponse(self, '000', data=model.json)
else:
return JsonResponse(self, '001', msg="你查询的记录不存在！")
else:
return JsonResponse(self, '100', msg="请传入参数id！")
elif ACTION == 'QUERY':
query = '''
select w.id,
w.itemid, -- 任务ID
w.desp, -- 工作内容
w.duration, -- 消耗时间
w.entry_date, -- 创建时间
u.fullname as create_user -- 日志记录人
from todo_work as w
left join secu_user as u on w.entry_user = u.id
where w.itemid = :itemid
order by w.identity desc limit {},{} ;
'''
count_query = '''
select count(1)
from todo_work as w
left join secu_user as u on w.entry_user = u.id
where w.itemid = :itemid;
'''
condition = {
'itemid': self.GET.get('itemid', ''),
}
record = self.GET.get('record')
pagesize = self.GET.get('pagesize', '10')
record = record if record else int(self.GET.get('pageindex',
0)) * int(pagesize)
query = query.format(record, pagesize)
count = self.db.execute(count_query, condition).scalar()
data = json_by_result(self.db.execute(query, condition).fetchall())
return JsonResponse(self,
'000',
data={
'count': count,
'list': data
})
elif ACTION == 'MYLOGS':
query = '''
SELECT w.id,
w.desp,
w.duration,
w.entry_date,
u.username,
i.title,
p.name as project_name
FROM todo_work w
JOIN secu_user u ON w.entry_user=u.id
LEFT JOIN todo_item i ON w.itemid=i.id
LEFT JOIN todo_project p ON i.projectid=p.id
where u.id = :userid and
(:projectid = '' or p.id = :projectid) and
(:item_name = '' or i.title like :item_name) and
(w.duration >= :min_duration) and
(w.duration <= :max_duration) and
datediff(w.entry_date,:min_entry_date)>=0 and
datediff(w.entry_date,:max_entry_date)<=0
order by w.entry_date desc limit {},{} ;
'''
count_query = '''
SELECT count(1)
FROM todo_work w
JOIN secu_user u ON w.entry_user=u.id
LEFT JOIN todo_item i ON w.itemid=i.id
LEFT JOIN todo_project p ON i.projectid=p.id
where u.id = :userid and
(:projectid = '' or p.id = :projectid) and
(:item_name = '' or i.title like :item_name) and
(w.duration >= :min_duration) and
(w.duration <= :max_duration) and
datediff(w.entry_date,:min_entry_date)>=0 and
datediff(w.entry_date,:max_entry_date)<=0;
'''
condition = {
'userid':
self.GET.get('userid', self.session['userid']),
'projectid':
self.GET.get('projectid', ''),
'item_name':
'' if not self.GET.get('item_name') else '%{}%'.format(
self.GET.get('item_name')),
'min_duration':
int(self.GET.get('min_duration', 0)),
'max_duration':
int(self.GET.get('max_duration', 999999)),
'min_entry_date':
self.GET.get('min_entry_date', '1900-1-1'),
'max_entry_date':
self.GET.get('max_entry_date', '2100-1-1')
}
record = self.GET.get('record')
pagesize = self.GET.get('pagesize', '10')
record = record if record else int(self.GET.get('pageindex',
0)) * int(pagesize)
query = query.format(record, pagesize)
count = self.db.execute(count_query, condition).scalar()
self.db.commit()
data = json_by_result(self.db.execute(query, condition).fetchall())
return JsonResponse(self,
'000',
data={
'count': count,
'list': data
})
else:
return JsonResponse(self, '100', msg="缺失参数ACTION")
def get(self):
ACTION = self.GET.get('ACTION', '')
if ACTION == 'ONE':
pk = self.GET.get('id')
if pk:
model = self.db.query(Item).filter(Item.id == pk).first()
if model:
ps = self.db.query(PersonInCharge).filter(
PersonInCharge.itemid == pk).order_by(
PersonInCharge.sequence.asc()).all()
data = model.json
data['person_in_charges'] = [i.json for i in ps]
return JsonResponse(self, '000', data=data)
else:
return JsonResponse(self, '001', msg="你查询的记录不存在！")
else:
return JsonResponse(self, '100', msg="请传入参数id！")
elif ACTION == 'QUERY':
query = '''
select p.id,
p.projectid,
pro.name as projectname,
p.title, -- 名称
p.cat, -- 类别
p.priority, -- 优先级
p.status, -- 当前状态
u.fullname as person_in_charge, -- 当前负责人
p.estimated_duration, -- 预估时间
p.entry_date, -- 创建时间
cu.fullname as create_user, -- 创建人
case when uw.itemid is not null then 1 else 0 end as isunderway,
case when f.itemid is not null then 1 else 0 end as isfavorite
from todo_item as p
left join todo_project as pro on p.projectid=pro.id
left join todo_person_in_charge as c on p.id = c.itemid and
p.status = c.itemstatus
left join secu_user as u on c.person_in_charge = u.id
left join secu_user as cu on p.entry_user = cu.id
left join todo_underway as uw on uw.itemid = p.id and uw.userid = :current_user
left join todo_favorite_item as f on f.itemid = p.id and f.userid = :current_user
where (:title = '' or p.title like :title) and
(:projectid = '' or p.projectid = :projectid) and
(:status = '' or p.status = :status) and
(:person_in_charge = '' or c.person_in_charge = :person_in_charge) and
(:cat = '' or p.cat = :cat) and
(:priority = '' or p.priority = :priority) and
(p.estimated_duration >= :min_estimated_duration) and
(p.estimated_duration <= :max_estimated_duration) and
(p.entry_date >= :min_entry_date) and
(p.entry_date <= :max_entry_date)
order by p.identity desc limit {},{} ;
'''
count_query = '''
select count(1)
from todo_item as p
left join todo_person_in_charge as c on p.id = c.itemid and
p.status = c.itemstatus
left join secu_user as u on c.person_in_charge = u.id
left join secu_user as cu on p.entry_user = cu.id
left join todo_underway as uw on uw.itemid = p.id and uw.userid = :current_user
left join todo_favorite_item as f on f.itemid = p.id and f.userid = :current_user
where (:title = '' or p.title like :title) and
(:projectid = '' or p.projectid = :projectid) and
(:status = '' or p.status = :status) and
(:person_in_charge = '' or c.person_in_charge = :person_in_charge) and
(:cat = '' or p.cat = :cat) and
(:priority = '' or p.priority = :priority) and
(p.estimated_duration >= :min_estimated_duration) and
(p.estimated_duration <= :max_estimated_duration) and
(p.entry_date >= :min_entry_date) and
(p.entry_date <= :max_entry_date);
'''
condition = {
'title':
'' if not self.GET.get('title') else '%{}%'.format(
self.GET.get('title')),
'projectid':
self.GET.get('projectid', ''),
'status':
self.GET.get('status', ''),
'person_in_charge':
self.GET.get('person_in_charge', ''),
'cat':
self.GET.get('cat', ''),
'priority':
self.GET.get('priority', ''),
'min_estimated_duration':
int(self.GET.get('min_estimated_duration', 0)),
'max_estimated_duration':
int(self.GET.get('max_estimated_duration', 999999)),
'min_entry_date':
self.GET.get('min_entry_date', '1900-1-1'),
'max_entry_date':
self.GET.get('max_entry_date', '2100-1-1'),
'current_user':
self.session['userid']
}
record = self.GET.get('record')
pagesize = self.GET.get('pagesize', '10')
if not record:
record = int(self.GET.get('pageindex', 0)) * int(pagesize)
query = query.format(record, pagesize)
count = self.db.execute(count_query, condition).scalar()
data = json_by_result(self.db.execute(query, condition).fetchall())
return JsonResponse(self,
'000',
data={
'count': count,
'list': data
})
elif ACTION == 'MYITEMS':
query = '''
select p.id,
p.projectid,
pro.name as projectname,
p.title, -- 名称
p.cat, -- 类别
p.priority, -- 优先级
p.status, -- 当前状态
u.fullname as person_in_charge, -- 当前负责人
p.estimated_duration, -- 预估时间
p.entry_date, -- 创建时间
cu.fullname as create_user, -- 创建人
case when uw.itemid is not null then 1 else 0 end as isunderway,
case when f.itemid is not null then 1 else 0 end as isfavorite
from todo_item as p
left join todo_project as pro on p.projectid=pro.id
left join todo_person_in_charge as c on p.id = c.itemid and
p.status = c.itemstatus
left join secu_user as u on c.person_in_charge = u.id
left join secu_user as cu on p.entry_user = cu.id
left join todo_underway as uw on uw.itemid = p.id and uw.userid = :current_user
left join todo_favorite_item as f on f.itemid = p.id and f.userid = :current_user
where p.id in (select distinct itemid from todo_person_in_charge where person_in_charge = :current_user) and
(:title = '' or p.title like :title) and
(:projectid = '' or p.projectid = :projectid) and
(:status = '' or p.status = :status) and
(:person_in_charge = '' or c.person_in_charge = :person_in_charge) and
(:cat = '' or p.cat = :cat) and
(:priority = '' or p.priority = :priority) and
(p.estimated_duration >= :min_estimated_duration) and
(p.estimated_duration <= :max_estimated_duration) and
(p.entry_date >= :min_entry_date) and
(p.entry_date <= :max_entry_date)
order by p.identity desc limit {},{} ;
'''
count_query = '''
select count(1)
from todo_item as p
left join todo_person_in_charge as c on p.id = c.itemid and
p.status = c.itemstatus
left join secu_user as u on c.person_in_charge = u.id
left join secu_user as cu on p.entry_user = cu.id
left join todo_underway as uw on uw.itemid = p.id and uw.userid = :current_user
left join todo_favorite_item as f on f.itemid = p.id and f.userid = :current_user
where p.id in (select distinct itemid from todo_person_in_charge where person_in_charge = :current_user) and
(:title = '' or p.title like :title) and
(:projectid = '' or p.projectid = :projectid) and
(:status = '' or p.status = :status) and
(:person_in_charge = '' or c.person_in_charge = :person_in_charge) and
(:cat = '' or p.cat = :cat) and
(:priority = '' or p.priority = :priority) and
(p.estimated_duration >= :min_estimated_duration) and
(p.estimated_duration <= :max_estimated_duration) and
(p.entry_date >= :min_entry_date) and
(p.entry_date <= :max_entry_date);
'''
condition = {
'title':
'' if not self.GET.get('title') else '%{}%'.format(
self.GET.get('title')),
'projectid':
self.GET.get('projectid', ''),
'status':
self.GET.get('status', ''),
'person_in_charge':
self.GET.get('person_in_charge', ''),
'cat':
self.GET.get('cat', ''),
'priority':
self.GET.get('priority', ''),
'min_estimated_duration':
int(self.GET.get('min_estimated_duration', 0)),
'max_estimated_duration':
int(self.GET.get('max_estimated_duration', 999999)),
'min_entry_date':
self.GET.get('min_entry_date', '1900-1-1'),
'max_entry_date':
self.GET.get('max_entry_date', '2100-1-1'),
'current_user':
self.session['userid']
}
record = self.GET.get('record')
pagesize = self.GET.get('pagesize', '10')
if not record:
record = int(self.GET.get('pageindex', 0)) * int(pagesize)
query = query.format(record, pagesize)
count = self.db.execute(count_query, condition).scalar()
data = json_by_result(self.db.execute(query, condition).fetchall())
return JsonResponse(self,
'000',
data={
'count': count,
'list': data
})
elif ACTION == 'UNDERWAY':
query = '''
select p.id,
p.projectid,
pro.name as projectname,
p.title, -- 名称
p.cat, -- 类别
p.priority, -- 优先级
p.status, -- 当前状态
u.fullname as person_in_charge, -- 当前负责人
p.estimated_duration, -- 预估时间
p.entry_date, -- 创建时间
cu.fullname as create_user, -- 创建人
u1.fullname as underway_user,
case when uw.itemid is not null then 1 else 0 end as isunderway,
case when f.itemid is not null then 1 else 0 end as isfavorite
from
todo_underway as uw0
left join todo_item as p on uw0.itemid = p.id
left join todo_project as pro on p.projectid=pro.id
left join todo_person_in_charge as c on p.id = c.itemid and
p.status = c.itemstatus
left join secu_user as u on c.person_in_charge = u.id
left join secu_user as cu on p.entry_user = cu.id
left join secu_user as u1 on uw0.userid = u1.id
left join todo_underway as uw on uw.itemid = p.id and uw.userid = :current_user
left join todo_favorite_item as f on f.itemid = p.id and f.userid = :current_user
where (:title = '' or p.title like :title) and
(:projectid = '' or p.projectid = :projectid) and
(:status = '' or p.status = :status) and
(:person_in_charge = '' or c.person_in_charge = :person_in_charge) and
(:underway_user = '' or uw0.userid = :underway_user) and
(:cat = '' or p.cat = :cat) and
(:priority = '' or p.priority = :priority) and
(p.estimated_duration >= :min_estimated_duration) and
(p.estimated_duration <= :max_estimated_duration) and
(p.entry_date >= :min_entry_date) and
(p.entry_date <= :max_entry_date)
order by uw0.itemid desc limit {},{} ;
'''
count_query = '''
select count(1)
from
todo_underway as uw0
left join todo_item as p on uw0.itemid = p.id
left join todo_person_in_charge as c on p.id = c.itemid and
p.status = c.itemstatus
left join secu_user as u on c.person_in_charge = u.id
left join secu_user as cu on p.entry_user = cu.id
left join secu_user as u1 on uw0.userid = u1.id
left join todo_underway as uw on uw.itemid = p.id and uw.userid = :current_user
left join todo_favorite_item as f on f.itemid = p.id and f.userid = :current_user
where (:title = '' or p.title like :title) and
(:projectid = '' or p.projectid = :projectid) and
(:status = '' or p.status = :status) and
(:person_in_charge = '' or c.person_in_charge = :person_in_charge) and
(:underway_user = '' or uw0.userid = :underway_user) and
(:cat = '' or p.cat = :cat) and
(:priority = '' or p.priority = :priority) and
(p.estimated_duration >= :min_estimated_duration) and
(p.estimated_duration <= :max_estimated_duration) and
(p.entry_date >= :min_entry_date) and
(p.entry_date <= :max_entry_date);
'''
condition = {
'title':
'' if not self.GET.get('title') else '%{}%'.format(
self.GET.get('title')),
'projectid':
self.GET.get('projectid', ''),
'status':
self.GET.get('status', ''),
'person_in_charge':
self.GET.get('person_in_charge', ''),
'cat':
self.GET.get('cat', ''),
'priority':
self.GET.get('priority', ''),
'min_estimated_duration':
int(self.GET.get('min_estimated_duration', 0)),
'max_estimated_duration':
int(self.GET.get('max_estimated_duration', 999999)),
'min_entry_date':
self.GET.get('min_entry_date', '1900-1-1'),
'max_entry_date':
self.GET.get('max_entry_date', '2100-1-1'),
'current_user':
self.session['userid'],
'underway_user':
self.GET.get('underway_user', '')
}
record = self.GET.get('record')
pagesize = self.GET.get('pagesize', '10')
if not record:
record = int(self.GET.get('pageindex', 0)) * int(pagesize)
query = query.format(record, pagesize)
count = self.db.execute(count_query, condition).scalar()
data = json_by_result(self.db.execute(query, condition).fetchall())
return JsonResponse(self,
'000',
data={
'count': count,
'list': data
})
elif ACTION == 'FAVORITE':
query = '''
select p.id,
p.projectid,
pro.name as projectname,
p.title, -- 名称
p.cat, -- 类别
p.priority, -- 优先级
p.status, -- 当前状态
u.fullname as person_in_charge, -- 当前负责人
p.estimated_duration, -- 预估时间
p.entry_date, -- 创建时间
cu.fullname as create_user, -- 创建人
case when uw.itemid is not null then 1 else 0 end as isunderway,
case when f.itemid is not null then 1 else 0 end as isfavorite
from
todo_favorite_item as f0
left join todo_item as p on f0.itemid = p.id
left join todo_project as pro on p.projectid=pro.id
left join todo_person_in_charge as c on p.id = c.itemid and
p.status = c.itemstatus
left join secu_user as u on c.person_in_charge = u.id
left join secu_user as cu on p.entry_user = cu.id
left join todo_underway as uw on uw.itemid = p.id and uw.userid = :current_user
left join todo_favorite_item as f on f.itemid = p.id and f.userid = :current_user
where f0.userid = :current_user and
(:title = '' or p.title like :title)
order by f0.itemid desc;
'''
condition = {
'title':
'' if not self.GET.get('title') else '%{}%'.format(
self.GET.get('title')),
'current_user':
self.session['userid']
}
data = json_by_result(self.db.execute(query, condition).fetchall())
return JsonResponse(self, '000', data=data)
elif ACTION == 'OPTIONS':
return JsonResponse(self, '000', data=Item.options)
else:
return JsonResponse(self, '100', msg="缺失参数ACTION")