2018年3月

首页2018年3月
28
Mar
0

django-rest-framework单元测试

常用命令
执行目录

下所有的测试(所有的test*.py文件):
$ python manage.py test
执行animals项目下tests包里的测试:
$ python manage.py test animals.tests
执行animals项目里的test测试:
$ python manage.py test animals
单独执行某个test case:
$ python manage.py test animals.tests.AnimalTestCase
单独执行某个测试方法:
$ python manage.py test animals.tests.AnimalTestCase.test_animals_can_speak
为测试文件提供路径:
$ python manage.py test animals/
通配测试文件名:
$ python manage.py test --pattern="tests_*.py"
启用warnings提醒:
$ python -Wall manage.py test

测试数据库需要对数据库先填充数据,填充的地方一般在setUp和setUpData

def setUp(self):
    pass
def exec_sql(sql):
    cursor = connection.cursor()
    cursor.execute(sql)
def setUpTestData():
    pass

对于接口的测试主要通过以下方法
主要引用单元

import SweetPy
from django.test import TestCase
from django.db import connection
from django.test.client import Client
from SweetPy.func_plus import FuncHelper
import json
from urllib import parse
from .common import *

测试代码如下:

class QueryTest(TestCase):
    def test_get_all_count1(self):
        all_count = get_all_count('149214')
        self.assertTrue(all_count == 3, '总数量不正确')
        completed_count = get_completed_count('149214')
        self.assertTrue(completed_count == 1, '完成数量不正确')
        not_ended_count = get_not_ended_count('149214')
        self.assertTrue(not_ended_count == 2, '待办数量不正确')
        defered_count = get_defered_count('149214')
        self.assertTrue(defered_count == 0, '延期数量不正确')

    def test_task_statistics(self):
        url = '/task/statistics/149214/'
        client = Client()
        response = client.get(url)
        result = FuncHelper.bytes_str_decode_str(response.content)
        result_json = json.loads(result)
        self.assertEqual(result_json['code'],'success',result_json['message'])
        self.assertEqual(result_json['data']['all'], 3, '查询总数量不正确')

默认测试数据库:

测试数据库的名字是test_DATABASE_NAME,DATABASE_NAME是你在settings.py里配置的数据库名.如果 你需要给测试数据库一个其他的名字,在settings.py中指定TEST_DATABASE_NAME的值。使用sqlite3时,数据库是在内存中创建的。
除了数据库是单独创建的以外,测试工具会使用相同的数据库配置--DATABASE_ENGINE, DATABASE_USER, DATABASE_HOST等等.创建测试数据库的用户DATABASE_USER(settings中)指定,所以你需要确认 DATABASE_USER有足够的权限去创建数据库

修改测试数据库
因为有时用第三方库 会导致创建很慢 可以手动修改掉测试数据库
if'test'insys.argv:DATABASES['default']={'ENGINE':'django.db.backends.sqlite3','NAME':'test_db'}

下面是各控件详细用法:
Making Requests

使用django.test.client.Client()来执行请求。

class Client(enforce_csrf_checks=False, **defaults)

可以使用关键字参数来指定默认的请求报头:

c = Client(HTTP_USER_AGENT='Mozilla/5.0')
记得在USER_AGENT前加HTTP_。

Client实例具有以下方法:

get(path, data={}, follow=False, **extra)

执行一个GET请求并返回Response对象。

c = Client()
c.get('/customers/details/', {'name': 'fred', 'age': 7})

相当于向以下url执行GET:

/customers/details/?name=fred&age=7
extra关键字参数可用作请求报头:

c = Client()
c.get('/customers/details/', {'name': 'fred', 'age': 7},

... HTTP_X_REQUESTED_WITH='XMLHttpRequest')
当然也可以将查询字符对编码后加入url:

c = Client()
c.get('/customers/details/?name=fred&age=7')

data参数的优先级在编码后的url之上。

如果将follow设置为True,client会追踪任何重定向,返回的response有redirect_chain属性,包括所有重定向过程中的url和状态码组成的元祖列表。

如果有个URL /redirect_me/ 重定向向 /next/, 再重定向向 /final/:

response = c.get('/redirect_me/', follow=True)
response.redirect_chain

[(u'http://testserver/next/', 302), (u'http://testserver/final/', 302)]
post(path, data={}, content_type=MULTIPART_CONTENT, follow=False, **extra)

执行一个POST请求并返回response对象,data参数为POST数据。

如果提供content_type参数(例如 text/xml),数据会被作为报头中Content-Type的类型进行POST上传。

如果不提供content_type参数,数据会被作为multipart/form-data类型上传。

为一个参数提交多个多个值时--比如选住select multiple域的多个值--这些值可以是列表或者元组.举例来说,提交choice域的三个被选中的值:

{'choices': ('a', 'b', 'd')}
上传文件:

c = Client()
with open('wishlist.doc') as fp:

... c.post('/customers/wishes/', {'name': 'fred', 'attachment': fp})
文件的名字'attachment'是不相关的,取决于你处理文件的代码。

如果同一个文件要post多次,注意每次post都要恢复文件的指针,最简单的方法就是将文件关闭再重新打开。

注意文件要以正确的方式被打开以便于读取,如果文件是binary data,例如读取img时,要将打开模式设为rb。

post的路径中也可以包含查询字符对:

c.post('/login/?visitor=true', {'name': 'fred', 'passwd': 'secret'})

这样既会通过post上传data数据,也向GET确定visitor=True。

options(path, data='', content_type='application/octet-stream', follow=False, **extra)

做OPTIONS请求,对测试REST接口很有用。data被用作请求的主体。

put(path, data='', content_type='application/octet-stream', follow=False, **extra)

做PUT请求,测试RESTful接口。

patch(path, data='', content_type='application/octet-stream', follow=False, **extra)

做PATCH请求,测试RESTful接口。

delete(path, data='', content_type='application/octet-stream', follow=False, **extra)

做DELETE请求,测试RESTful接口。

login(**credentials)

如果使用django的用户验证系统,可用login方法进行测试。

c = Client()
c.login(username='fred', password='secret')

登陆成功的话,返回True。

使用之前,当然要创建一个用户。由于测试数据库使用的是单独的数据库,原先数据库中的用户是不能用于测试的。

设置密码时,不能用user的密码属性进行设置,而是用set_password()方法设置正确的哈希密码,或者使用create_user()方法创建一个带哈希密码的用户。

logout()

登出。

Testing Responses

client的get和post方法都返回response对象,和HttpResponse对象是不同的。

class Response具有以下属性:

client:the test client

content:response的主体,string类型,是view render后的页面的最终内容,或者是错误信息。

context:用来渲染模板的context实例。如果页面使用了多个模板,那context就会是Context Object列表.它们的排序方式就是它们被渲染的顺序。

response = client.get('/foo/')
response.context['name']

'Arthur'
request:用于请求的数据。

status_code:状态码。

templates:被用来渲染最终的content的Template实例列表.template.name可以得到template的文件名,如果template是由文件载入的话(如 'admin/index.html')。那template就会是Template列表,它们的排序方式就是它们被渲染的顺序.

response也可以当做字典来查询Http header:

response['Content-Type']
Exceptions

如果你将TestClient指向了由view函数raise的异常,那这个异常在test case里是可见的.你可以使用标准的try...except块或者assertRaises()来测试它们.对test client唯一不可见的异常是Http404,PermissionDenied和SystemExit。django会在内部捕捉这些异常并返回合适的response.这种情况下,你可以查看下你的response.status_code.

Persistent state

如果一个response返回了一个cookie,那么这个cookie就会被存储在test client里,并被其后的所有get()和post()传送.如果你想要终止这个cookie,你可以新建一个Client实例,或者手动删除它。

一个test client具有两个存储持久化状态信息的属性:

Client.cookies

一个python SimpleCookie对象,存储cilent的所有cookie。

Client.sessions

包含session信息的类字典对象。

如果要修改一个session并且存储,首先将session存储在变量中:

def test_something(self):

session = self.client.session
session['somekey'] = 'test'
session.save()

一个使用client进行测试的实例:
from django.utils import unittest
from django.test.client import Client

class SimpleTest(unittest.TestCase):

def setUp(self):
    # Every test needs a client.
    self.client = Client()

def test_details(self):
    # Issue a GET request.
    response = self.client.get('/customer/details/')

    # Check that the response is 200 OK.
    self.assertEqual(response.status_code, 200)

    # Check that the rendered context contains 5 customers.
    self.assertEqual(len(response.context['customers']), 5)

Test cases的一些功能
默认的test client

每个django.test.*TestCase的test case实例都会访问django test client,所以Client可以不用实例化,而直接用self.client访问:

from django.test import TestCase

class SimpleTest(TestCase):

def test_details(self):
    response = self.client.get('/customer/details/')
    self.assertEqual(response.status_code, 200)

def test_index(self):
    response = self.client.get('/customer/index/')
    self.assertEqual(response.status_code, 200)

Fixture loading

如果数据库里没有数据,那么对于一个基于数据库的网站来说,test case并无多大的用处.为了给测试数据库加入测试数据更方便,django提供了载入fixtures的方法.

fixture是一系列的数据集合,django知道如何将它导入数据库。

创建fixture最直接的方法就是使用manage.py dumpdata.当然,这假设你的实际数据库里已经有数据了.

注意:
如果你运行过manage.py syncdb命令,那么你已经使用过fixture了--只是你不知道而已。当你使用syncdb去创建数据库时,会创建一个叫initial_data的fixture。
其他名字的Fixture可以通过manage.py loaddata命令手动安装.

一旦建立了一个fixture,并将它放在了某个django app的fixtures目录中,你就可以在你的测试类里使用它了:

from django.test import TestCase
from myapp.models import Animal

class AnimalTestCase(TestCase):

fixtures = ['mammals.json', 'birds']

def setUp(self):
    # Test definitions as before.
    call_setup_methods()

def testFluffyAnimals(self):
    # A test that uses the fixtures.
    call_some_test_code()

这是具体发生的过程:

  1. 在setup()运行前,django会清空数据库,相当于你执行了syncdb。
    2.然后,所有的fixture会被安装.在例子中,django会安装任何一个名字为mammals的JSON格式的fixture和名为birds的fixture数据。

Assertions

除了python中的assertEqual()和assertTrue()外,django的TestCase还提供了几个额外的assert方法。

assertContains(response, text, count=None, status_code=200, msg_prefix='', html=False)

断言response是否与status_code和text内容相应。将html设为True会将text作为html处理。

assertJSONEqual(raw, expected_data, msg=None)

断言Json片段raw和expected_data是否相当。

24
Mar
0

ubuntu16.4下安装java,eclipse方法,无法获得锁 /var/lib/dpkg/lock -open

java安装方法:

sudo add-apt-repository ppa:webupd8team/java
sudo apt-get install oracle-java8-installer
sudo apt-get update
sudo apt-get install oracle-java8-installer

无法获得锁 /var/lib/dpkg/lock -open的解锁命令:

sudo rm /var/cache/apt/archives/lock
sudo rm /var/lib/dpkg/lock

ps aux|grep yum
kill -9 xxx

安装ECLIPSE:

sudo apt-get install oracle-java8-installer
sudo apt-get install eclipse
22
Mar
0

jira绕过认证系统登陆和部分表说明

最近需要对jira进行公司内部sso集成,经多方了解,最后写插件才解决问题

主要流程是先下载安装plugin sdk包然后按照
https://developer.atlassian.com/server/framework/atlassian-sdk/create-a-helloworld-plugin-project/
页面的步骤操作,过程有点慢,因为这里创建基础框架的时候会下载一大堆的相关东西
命令有:
atlas-create-jira-plugin 创建基础框架
atlas-run 执行插件
默认帐号密码都为admin

然后在位置/src/main/java/com.atlassian.tutorial/目录创建
SSOFilter.java文件

package com.atlassian.tutorial;
import javax.servlet.*;
import javax.servlet.http.HttpServletRequest;
import java.io.IOException;
import java.security.Principal;

public class SSOFilter implements Filter {
    public SSOFilter() {
    }
    @Override
    public void init(FilterConfig filterConfig) throws ServletException {
    }
    @Override
    public void doFilter(ServletRequest servletRequest, ServletResponse servletResponse, FilterChain filterChain) throws IOException, ServletException {
        //TODO GEELY-UC-SSO-SDK
        //TODO 调用SSO认证服务,根据TOKEN获取用户标示(域账号)
        //TODO 调用JIRA认证接口,构建回话
        //TODO SESSION KEY: ASESSIONID
        simuldateLogin((HttpServletRequest)servletRequest);
        filterChain.doFilter(servletRequest, servletResponse);
    }
    protected void simuldateLogin(HttpServletRequest request) {
            Principal principal = new Principal() {
                @Override
                public String getName() {
                    return "admin";
                }
            };
            request.getSession().setAttribute("seraph_defaultauthenticator_user", principal);
            request.getSession().setAttribute("seraph_defaultauthenticator_logged_out_user", (Object)null);
    }
    @Override
    public void destroy() {
    }
}

返回什么名称 就直接登陆jira系统里有的用户
然后找到/src/main/resources/atlassian-plugin.xml文件修改节点

  <servlet-filter name="Hello World Servlet" key="helloWorld" class="com.atlassian.tutorial.SSOFilter" location="after-encoding" weight="200">
    <description>Says Hello World, Australia or your name.</description>
    <url-pattern>/*</url-pattern>
    <dispatcher>REQUEST</dispatcher>
    <dispatcher>FORWARD</dispatcher>
  </servlet-filter>

location 设置插件执行时机
weight 同类插件中的权重

表说明,就不说明了,直接上sql语句慢慢理解吧 哈哈
-- 根据PKEY取到issue
select jiraissue.* from jiraissue
INNER JOIN project_key on (jiraissue.project = project_key.project_id and project_key = 'SDLC')
where issuenum = 1284

-- 根据KR主任务关联ISSUE 单层
select jiraissue.* from jiraissue
where id in(
select source from issuelink where destination = 149214 and linktype in (select id from issuelinktype where linkname = 'Blocks'))

-- 根据KR主任务关联ISSUE 第三层
select jiraissue.* from jiraissue
where id in(
select destination from issuelink where source = 163121 and linktype in (select id from issuelinktype where linkname = 'jira_subtask_link'))

-- 根据KR主任务关联ISSUE 三层
select distinct t1.id as jiraId,summary as title,description as content,(select pname from priority where priority.id = t1.priority) priority,
assignee as executor,(select pname from issuestatus where issuestatus.id = t1.issuestatus) as state,
(select datevalue from customfieldvalue where customfieldvalue.issue = t1.id and customfieldvalue.customfield in(select id from customfield where cfname like '计划开始时间')) as 计划开始时间,
duedate as 计划到期日,
(select changegroup.created from changeitem , changegroup where changegroup.issueid = t1.id and changeitem.oldvalue = 10000 and changegroup.id = changeitem.groupid limit 1) as 实际开始时间,
resolutiondate as 实际完成时间 from (
select jiraissue.* from jiraissue
where id in(
select source from issuelink where destination = 149214 and linktype in (select id from issuelinktype where linkname = 'Blocks'))
union
select jiraissue.* from jiraissue
where id in(
select destination from issuelink where source in (select jiraissue.id from jiraissue
where id in(
select source from issuelink where destination = 149214 and linktype in (select id from issuelinktype where linkname = 'Blocks'))
) and linktype in (select id from issuelinktype where linkname = 'jira_subtask_link')))as t1

-- 统计数量
select distinct t1.id as jiraId,summary as title,description as content,(select pname from priority where priority.id = t1.priority) priority,
assignee as executor,(select pname from issuestatus where issuestatus.id = t1.issuestatus) as state,
(select datevalue from customfieldvalue where customfieldvalue.issue = t1.id and customfieldvalue.customfield in(select id from customfield where cfname like '计划开始时间')) as 计划开始时间,
duedate as 计划到期日,
(select changegroup.created from changeitem , changegroup where changegroup.issueid = t1.id and changeitem.oldvalue = 10000 and changegroup.id = changeitem.groupid limit 1) as 实际开始时间,
resolutiondate as 实际完成时间 from (
select jiraissue.* from jiraissue
where id in(
select source from issuelink where destination = 149214 and linktype in (select id from issuelinktype where linkname = 'Blocks'))
union
select jiraissue.* from jiraissue
where id in(
select destination from issuelink where source in (select jiraissue.id from jiraissue
where id in(
select source from issuelink where destination = 149214 and linktype in (select id from issuelinktype where linkname = 'Blocks'))
) and linktype in (select id from issuelinktype where linkname = 'jira_subtask_link')))as t1

-- 所有数量
select count(*) from (
select jiraissue.* from jiraissue
where id in(
select source from issuelink where destination = 149214 and linktype in (select id from issuelinktype where linkname = 'Blocks'))
union
select jiraissue.* from jiraissue
where id in(
select destination from issuelink where source in (select jiraissue.id from jiraissue
where id in(
select source from issuelink where destination = 149214 and linktype in (select id from issuelinktype where linkname = 'Blocks'))
) and linktype in (select id from issuelinktype where linkname = 'jira_subtask_link')))as t1

-- 未结束数量
select count(*) as f_count from(
select distinct t1.id as jiraId,summary as title,description as content,(select pname from priority where priority.id = t1.priority) priority,
assignee as executor,(select pname from issuestatus where issuestatus.id = t1.issuestatus) as state,
(select datevalue from customfieldvalue where customfieldvalue.issue = t1.id and customfieldvalue.customfield in(select id from customfield where cfname like '计划开始时间')) as 计划开始时间,
duedate as 计划到期日,
(select changegroup.created from changeitem , changegroup where changegroup.issueid = t1.id and changeitem.oldvalue = 10000 and changegroup.id = changeitem.groupid limit 1) as 实际开始时间,
resolutiondate as 实际完成时间 from (
select jiraissue.* from jiraissue
where id in(
select source from issuelink where destination = 149214 and linktype in (select id from issuelinktype where linkname = 'Blocks'))
union
select jiraissue.* from jiraissue
where id in(
select destination from issuelink where source in (select jiraissue.id from jiraissue
where id in(
select source from issuelink where destination = 149214 and linktype in (select id from issuelinktype where linkname = 'Blocks'))
) and linktype in (select id from issuelinktype where linkname = 'jira_subtask_link')))as t1) as t10 where 实际完成时间 is NULL

-- 延期数量
select * from(
select distinct t1.id as jiraId,summary as title,description as content,(select pname from priority where priority.id = t1.priority) priority,
assignee as executor,(select pname from issuestatus where issuestatus.id = t1.issuestatus) as state,
(select datevalue from customfieldvalue where customfieldvalue.issue = t1.id and customfieldvalue.customfield in(select id from customfield where cfname like '计划开始时间')) as 计划开始时间,
duedate as 计划到期日,
(select changegroup.created from changeitem , changegroup where changegroup.issueid = t1.id and changeitem.oldvalue = 10000 and changegroup.id = changeitem.groupid limit 1) as 实际开始时间,
resolutiondate as 实际完成时间 from (
select jiraissue.* from jiraissue
where id in(
select source from issuelink where destination = 149214 and linktype in (select id from issuelinktype where linkname = 'Blocks'))
union
select jiraissue.* from jiraissue
where id in(
select destination from issuelink where source in (select jiraissue.id from jiraissue
where id in(
select source from issuelink where destination = 149214 and linktype in (select id from issuelinktype where linkname = 'Blocks'))
) and linktype in (select id from issuelinktype where linkname = 'jira_subtask_link')))as t1) as t10 where str_to_date(CONCAT(DATE_FORMAT(计划到期日, '%Y-%m-%d'),'23:59:59'), '%Y-%m-%d %T') < 实际完成时间

-- 已结束数量
select count(*) as f_count from(
select distinct t1.id as jiraId,summary as title,description as content,(select pname from priority where priority.id = t1.priority) priority,
assignee as executor,(select pname from issuestatus where issuestatus.id = t1.issuestatus) as state,
(select datevalue from customfieldvalue where customfieldvalue.issue = t1.id and customfieldvalue.customfield in(select id from customfield where cfname like '计划开始时间')) as 计划开始时间,
duedate as 计划到期日,
(select changegroup.created from changeitem , changegroup where changegroup.issueid = t1.id and changeitem.oldvalue = 10000 and changegroup.id = changeitem.groupid limit 1) as 实际开始时间,
resolutiondate as 实际完成时间 from (
select jiraissue.* from jiraissue
where id in(
select source from issuelink where destination = 149214 and linktype in (select id from issuelinktype where linkname = 'Blocks'))
union
select jiraissue.* from jiraissue
where id in(
select destination from issuelink where source in (select jiraissue.id from jiraissue
where id in(
select source from issuelink where destination = 149214 and linktype in (select id from issuelinktype where linkname = 'Blocks'))
) and linktype in (select id from issuelinktype where linkname = 'jira_subtask_link')))as t1) as t10 where 实际完成时间 is not NULL

-- 已结束数据
select jiraId,title,content,priority,executor,实际开始时间 as startdate,实际完成时间 as enddate,state,jirapkey as detailurl from(
select t1.id as jiraId,summary as title,description as content,(select pname from priority where priority.id = t1.priority) as priority,
assignee as executor,
(select datevalue from customfieldvalue where customfieldvalue.issue = t1.id and customfieldvalue.customfield in(select id from customfield where cfname like '计划开始时间')) as 计划开始时间,
duedate as 计划到期日,
(select changegroup.created from changeitem , changegroup where changegroup.issueid = t1.id and changeitem.oldvalue = 10000 and changegroup.id = changeitem.groupid limit 1) as 实际开始时间,
resolutiondate as 实际完成时间,(select pname from issuestatus where issuestatus.id = t1.issuestatus) as state,jirapkey from (
select jiraissue.*,concat(project.pkey,'-',jiraissue.issuenum) as jirapkey from jiraissue
left join project on (project.id = jiraissue.project)
where jiraissue.id in(
select source from issuelink where destination = 149214 and linktype in (select id from issuelinktype where linkname = 'Blocks'))
union
select jiraissue.*,concat(project.pkey,'-',jiraissue.issuenum) as jirapkey from jiraissue
inner join project on (project.id = jiraissue.project)
where jiraissue.id in(
select destination from issuelink where source in (select jiraissue.id from jiraissue
where id in(
select source from issuelink where destination = 149214 and linktype in (select id from issuelinktype where linkname = 'Blocks'))
) and linktype in (select id from issuelinktype where linkname = 'jira_subtask_link')))as t1
) as t10 where 实际完成时间 is not NULL

-- 未结束数据
select jiraId,title,content,priority,executor,实际开始时间 as startdate,实际完成时间 as enddate,state,jirapkey as detailurl from(
select t1.id as jiraId,summary as title,description as content,(select pname from priority where priority.id = t1.priority) as priority,
assignee as executor,
(select datevalue from customfieldvalue where customfieldvalue.issue = t1.id and customfieldvalue.customfield in(select id from customfield where cfname like '计划开始时间')) as 计划开始时间,
duedate as 计划到期日,
(select changegroup.created from changeitem , changegroup where changegroup.issueid = t1.id and changeitem.oldvalue = 10000 and changegroup.id = changeitem.groupid limit 1) as 实际开始时间,
resolutiondate as 实际完成时间,(select pname from issuestatus where issuestatus.id = t1.issuestatus) as state,jirapkey from (
select jiraissue.*,concat(project.pkey,'-',jiraissue.issuenum) as jirapkey from jiraissue
left join project on (project.id = jiraissue.project)
where jiraissue.id in(
select source from issuelink where destination = 149214 and linktype in (select id from issuelinktype where linkname = 'Blocks'))
union
select jiraissue.*,concat(project.pkey,'-',jiraissue.issuenum) as jirapkey from jiraissue
inner join project on (project.id = jiraissue.project)
where jiraissue.id in(
select destination from issuelink where source in (select jiraissue.id from jiraissue
where id in(
select source from issuelink where destination = 149214 and linktype in (select id from issuelinktype where linkname = 'Blocks'))
) and linktype in (select id from issuelinktype where linkname = 'jira_subtask_link')))as t1
) as t10 where 实际完成时间 is NULL

-- 延期数据
select jiraId,title,content,priority,executor,实际开始时间 as startdate,实际完成时间 as enddate,state,jirapkey as detailurl from(
select t1.id as jiraId,summary as title,description as content,(select pname from priority where priority.id = t1.priority) as priority,
assignee as executor,
(select datevalue from customfieldvalue where customfieldvalue.issue = t1.id and customfieldvalue.customfield in(select id from customfield where cfname like '计划开始时间')) as 计划开始时间,
duedate as 计划到期日,
(select changegroup.created from changeitem , changegroup where changegroup.issueid = t1.id and changeitem.oldvalue = 10000 and changegroup.id = changeitem.groupid limit 1) as 实际开始时间,
resolutiondate as 实际完成时间,(select pname from issuestatus where issuestatus.id = t1.issuestatus) as state,jirapkey from (
select jiraissue.*,concat(project.pkey,'-',jiraissue.issuenum) as jirapkey from jiraissue
left join project on (project.id = jiraissue.project)
where jiraissue.id in(
select source from issuelink where destination = 149214 and linktype in (select id from issuelinktype where linkname = 'Blocks'))
union
select jiraissue.*,concat(project.pkey,'-',jiraissue.issuenum) as jirapkey from jiraissue
inner join project on (project.id = jiraissue.project)
where jiraissue.id in(
select destination from issuelink where source in (select jiraissue.id from jiraissue
where id in(
select source from issuelink where destination = 149214 and linktype in (select id from issuelinktype where linkname = 'Blocks'))
) and linktype in (select id from issuelinktype where linkname = 'jira_subtask_link')))as t1
) as t10 where str_to_date(CONCAT(DATE_FORMAT(计划到期日, '%Y-%m-%d'),'23:59:59'), '%Y-%m-%d %T') < 实际完成时间

-- 所有数据
select jiraId,title,content,priority,executor,实际开始时间 as startdate,实际完成时间 as enddate,state,jirapkey as detailurl from(
select t1.id as jiraId,summary as title,description as content,(select pname from priority where priority.id = t1.priority) as priority,
assignee as executor,
(select datevalue from customfieldvalue where customfieldvalue.issue = t1.id and customfieldvalue.customfield in(select id from customfield where cfname like '计划开始时间')) as 计划开始时间,
duedate as 计划到期日,
(select changegroup.created from changeitem , changegroup where changegroup.issueid = t1.id and changeitem.oldvalue = 10000 and changegroup.id = changeitem.groupid limit 1) as 实际开始时间,
resolutiondate as 实际完成时间,(select pname from issuestatus where issuestatus.id = t1.issuestatus) as state,jirapkey from (
select jiraissue.*,concat(project.pkey,'-',jiraissue.issuenum) as jirapkey from jiraissue
left join project on (project.id = jiraissue.project)
where jiraissue.id in(
select source from issuelink where destination = 149214 and linktype in (select id from issuelinktype where linkname = 'Blocks'))
union
select jiraissue.*,concat(project.pkey,'-',jiraissue.issuenum) as jirapkey from jiraissue
inner join project on (project.id = jiraissue.project)
where jiraissue.id in(
select destination from issuelink where source in (select jiraissue.id from jiraissue
where id in(
select source from issuelink where destination = 149214 and linktype in (select id from issuelinktype where linkname = 'Blocks'))
) and linktype in (select id from issuelinktype where linkname = 'jira_subtask_link')))as t1
) as t10

-- 根据用户查询所有数据
select jiraId,title,content,priority,executor,实际开始时间 as startdate,实际完成时间 as enddate,state,jirapkey as detailurl from(
select t1.id as jiraId,summary as title,description as content,(select pname from priority where priority.id = t1.priority) as priority,
assignee as executor,
(select datevalue from customfieldvalue where customfieldvalue.issue = t1.id and customfieldvalue.customfield in(select id from customfield where cfname like '计划开始时间')) as 计划开始时间,
duedate as 计划到期日,
(select changegroup.created from changeitem , changegroup where changegroup.issueid = t1.id and changeitem.oldvalue = 10000 and changegroup.id = changeitem.groupid limit 1) as 实际开始时间,
resolutiondate as 实际完成时间,(select pname from issuestatus where issuestatus.id = t1.issuestatus) as state,jirapkey from (
select jiraissue.*,concat(project.pkey,'-',jiraissue.issuenum) as jirapkey from jiraissue
left join project on (project.id = jiraissue.project)
where jiraissue.id in(
select source from issuelink where destination in(select id from jiraissue where project = 10404 and issuetype = 11500) and linktype in (select id from issuelinktype where linkname = 'Blocks'))
union
select jiraissue.*,concat(project.pkey,'-',jiraissue.issuenum) as jirapkey from jiraissue
inner join project on (project.id = jiraissue.project)
where jiraissue.id in(
select destination from issuelink where source in (select jiraissue.id from jiraissue
where id in(
select source from issuelink where destination in(select id from jiraissue where project = 10404 and issuetype = 11500) and linktype in (select id from issuelinktype where linkname = 'Blocks'))
) and linktype in (select id from issuelinktype where linkname = 'jira_subtask_link')))as t1
) as t10 where t10.executor = 'huang.xiaogang'

-- 根据用户查询未结束数据
select jiraId,title,content,priority,executor,实际开始时间 as startdate,实际完成时间 as enddate,state,jirapkey as detailurl from(
select t1.id as jiraId,summary as title,description as content,(select pname from priority where priority.id = t1.priority) as priority,
assignee as executor,
(select datevalue from customfieldvalue where customfieldvalue.issue = t1.id and customfieldvalue.customfield in(select id from customfield where cfname like '计划开始时间')) as 计划开始时间,
duedate as 计划到期日,
(select changegroup.created from changeitem , changegroup where changegroup.issueid = t1.id and changeitem.oldvalue = 10000 and changegroup.id = changeitem.groupid limit 1) as 实际开始时间,
resolutiondate as 实际完成时间,(select pname from issuestatus where issuestatus.id = t1.issuestatus) as state,jirapkey from (
select jiraissue.*,concat(project.pkey,'-',jiraissue.issuenum) as jirapkey from jiraissue
left join project on (project.id = jiraissue.project)
where jiraissue.id in(
select source from issuelink where destination in(select id from jiraissue where project = 10404 and issuetype = 11500 and issuestatus not in (select id from issuestatus where pname = 'Closed')) and linktype in (select id from issuelinktype where linkname = 'Blocks'))
and issuestatus not in (select id from issuestatus where pname = 'Closed')
union
select jiraissue.*,concat(project.pkey,'-',jiraissue.issuenum) as jirapkey from jiraissue
inner join project on (project.id = jiraissue.project)
where jiraissue.id in(
select destination from issuelink where source in (select jiraissue.id from jiraissue
where id in(
select source from issuelink where destination in(select id from jiraissue where project = 10404 and issuetype = 11500 and issuestatus not in (select id from issuestatus where pname = 'Closed')) and linktype in (select id from issuelinktype where linkname = 'Blocks'))
) and linktype in (select id from issuelinktype where linkname = 'jira_subtask_link'))
and issuestatus not in (select id from issuestatus where pname = 'Closed')
)as t1
) as t10 where t10.executor = 'huang.xiaogang' and 实际完成时间 is null