-
Notifications
You must be signed in to change notification settings - Fork 4
/
class.db.Mobo.php
142 lines (138 loc) · 7.17 KB
/
class.db.Mobo.php
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
<?php
require_once "class.DBConn.php";
require_once "config.php";
class MoboDB extends DBConn{
/**
* 查询已关闭和未关闭的BUG数
*/
public function selectBugOandC1(){
$conn=parent::getConn(DB_DSN_MOBO_JIRA,DB_MOBO_JIRA_USERNAME,DB_MOBO_JIRA_PASSWORD);
$sql='SELECT CASE issuestatus WHEN 6 THEN "关闭" ELSE "未关闭" END AS `name`,COUNT(ID) AS `y`,' .
'CASE issuestatus WHEN 6 THEN "#6A9AC2" ELSE "#66BA7C " END AS `color` FROM jiraissue ji WHERE ' .
'project IN (10100) GROUP BY `name` ORDER BY issuestatus DESC;';
try{
$st=$conn->prepare($sql);
$st->execute();
$row=$st->fetchAll();
}catch(PDOException $e){
echo "failure:".$e->getMessage();
return false;
}
return $row;
}
/**
* 查询已关闭和未关闭的BUG数
*/
public function selectBugOandC2(){
$conn=parent::getConn(DB_DSN_MOBO_JIRA,DB_MOBO_JIRA_USERNAME,DB_MOBO_JIRA_PASSWORD);
$sql=" SELECT CONCAT('当前版本(',(SELECT vname FROM projectversion WHERE project = 10100 AND DESCRIPTION NOT LIKE '%not begin%' ORDER BY vname DESC LIMIT 1),') ') AS `name`,COUNT(ji.id) AS `y`,'#6A9AC2' AS `color` FROM jiraissue ji INNER JOIN nodeassociation nn ON ji.id = nn.SOURCE_NODE_ID INNER JOIN projectversion pn ON pn.id = nn.SINK_NODE_ID WHERE ji.project IN (10100) AND nn.ASSOCIATION_TYPE='IssueVersion' AND ji.issuestatus = 6 AND pn.vname = (SELECT vname FROM projectversion WHERE project = 10100 AND DESCRIPTION NOT LIKE '%not begin%' ORDER BY vname DESC LIMIT 1) UNION SELECT '历史版本' AS `name`, (SELECT COUNT(ID) FROM jiraissue ji WHERE project IN (10100) AND ji.issuestatus = 6 ) - ( SELECT COUNT(ji.id) AS `y` FROM jiraissue ji INNER JOIN nodeassociation nn ON ji.id = nn.SOURCE_NODE_ID INNER JOIN projectversion pn ON pn.id = nn.SINK_NODE_ID WHERE ji.project IN (10100) AND nn.ASSOCIATION_TYPE='IssueVersion' AND ji.issuestatus = 6 AND pn.vname = (SELECT vname FROM projectversion WHERE project = 10100 AND DESCRIPTION NOT LIKE '%not begin%' ORDER BY vname DESC LIMIT 1) )AS `y`,'#6A9AC2' AS `color` UNION SELECT CONCAT('当前版本(',(SELECT vname FROM projectversion WHERE project = 10100 AND DESCRIPTION NOT LIKE '%not begin%' ORDER BY vname DESC LIMIT 1),') ') AS `name`,COUNT(ji.id) AS `y`,'#66BA7C ' AS `color` FROM jiraissue ji INNER JOIN nodeassociation nn ON ji.id = nn.SOURCE_NODE_ID INNER JOIN projectversion pn ON pn.id = nn.SINK_NODE_ID WHERE ji.project IN (10100) AND nn.ASSOCIATION_TYPE='IssueVersion' AND ji.issuestatus != 6 AND pn.vname = (SELECT vname FROM projectversion WHERE project = 10100 AND DESCRIPTION NOT LIKE '%not begin%' ORDER BY vname DESC LIMIT 1) UNION SELECT '历史版本' AS `name`, (SELECT COUNT(ID) FROM jiraissue ji WHERE project IN (10100) AND ji.issuestatus != 6 ) - ( SELECT COUNT(ji.id) AS `y` FROM jiraissue ji INNER JOIN nodeassociation nn ON ji.id = nn.SOURCE_NODE_ID INNER JOIN projectversion pn ON pn.id = nn.SINK_NODE_ID WHERE ji.project IN (10100) AND nn.ASSOCIATION_TYPE='IssueVersion' AND ji.issuestatus != 6 AND pn.vname = (SELECT vname FROM projectversion WHERE project = 10100 AND DESCRIPTION NOT LIKE '%not begin%' ORDER BY vname DESC LIMIT 1) )AS `y`,'#66BA7C ' AS `color` ;";
try{
$st=$conn->prepare($sql);
$st->execute();
$row=$st->fetchAll();
}catch(PDOException $e){
echo "failure:".$e->getMessage();
return false;
}
return $row;
}
/**
* 查询未关闭缺陷
*/
public function selectOpenBug(){
$conn=parent::getConn(DB_DSN_MOBO_JIRA,DB_MOBO_JIRA_USERNAME,DB_MOBO_JIRA_PASSWORD);
$sql="SELECT pv.vname AS `version`, RIGHT(cv.STRINGVALUE, 1) p, SUM(CASE ji.issuestatus WHEN 5 THEN 0.1 ELSE 1 END) AS num FROM jiraissue ji JOIN nodeassociation nn ON ji.id = nn.SOURCE_NODE_ID JOIN projectversion pv ON pv.id = nn.SINK_NODE_ID JOIN customfieldvalue cv ON cv.ISSUE = ji.ID WHERE ji.project IN (10100) AND pv.PROJECT IN (10100) AND nn.SINK_NODE_ENTITY = 'Version' AND nn.ASSOCIATION_TYPE = 'IssueVersion' AND ji.issuetype = 1 AND ji.issuestatus NOT IN (6) AND cv.CUSTOMFIELD = 10047 GROUP BY `version`, p ORDER BY `version`, p ;";
try{
$st=$conn->prepare($sql);
$st->execute();
$row=$st->fetchAll(); //MYSQL_NUM MYSQL_BOTH MYSQL_ASSOC
//print_r($row);
}catch(PDOException $e){
echo "failure:".$e->getMessage();
return false;
}
return $row;
}
/**
* 查询每个人身上的BUG情况
*/
public function selectBugEveryOne(){
$conn=parent::getConn(DB_DSN_MOBO_JIRA,DB_MOBO_JIRA_USERNAME,DB_MOBO_JIRA_PASSWORD);
$sql="SELECT ";
$sql.="cu.display_name AS `name`,ji.issuestatus AS `status`,COUNT(ji.ID) AS `value` " ;
$sql.="FROM jiraissue ji " ;
$sql.="JOIN nodeassociation nn ON ji.id = nn.SOURCE_NODE_ID " ;
$sql.="JOIN projectversion pn ON pn.id = nn.SINK_NODE_ID " ;
$sql.="JOIN cwd_user cu ON ji.assignee = cu.user_name " ;
$sql.="WHERE ji.project IN (10100) AND ji.issuetype = 1 AND nn.ASSOCIATION_TYPE='IssueVersion' AND pn.vname = (SELECT vname FROM projectversion WHERE project = 10100 AND DESCRIPTION NOT LIKE '%not begin%' ORDER BY vname DESC LIMIT 1) " ;
$sql.="GROUP BY cu.display_name,ji.issuestatus " ;
$sql.="ORDER BY cu.display_name,ji.issuestatus " ;
try{
$st=$conn->prepare($sql);
$st->execute();
$row=$st->fetchAll();
}catch(PDOException $e){
echo "failure:".$e->getMessage();
return false;
}
return $row;
}
/**
* 查询每个人身上的BUG情况
*/
public function selectTaskDistribution(){
$conn=parent::getConn(DB_DSN_MOBO_JIRA,DB_MOBO_JIRA_USERNAME,DB_MOBO_JIRA_PASSWORD);
$sql="SELECT ";
$sql.="pv.vname AS `version`,`is`.pname AS `status`,cu.display_name AS `assign`,ji.summary AS `sub` ";
$sql.="FROM jiraissue ji ";
$sql.="JOIN customfieldvalue cv ON ji.id = cv.issue ";
$sql.="JOIN projectversion pv ON cv.NUMBERVALUE = pv.id ";
$sql.="JOIN issuestatus `is` ON ji.issuestatus = `is`.id ";
$sql.="LEFT JOIN cwd_user cu ON ji.assignee= cu.user_name ";
$sql.="WHERE ji.issuetype IN(3,5) AND ji.project IN (10120) AND cv.customfield = 10029 ";
$sql.="ORDER BY `version`,`status`,`assign` ";
try{
$st=$conn->prepare($sql);
$st->execute();
$row=$st->fetchAll();
}catch(PDOException $e){
echo "failure:".$e->getMessage();
return false;
}
return $row;
}
/**
* 各版本BUG清除率
*/
public function selectDefectRemovalRate(){
$conn=parent::getConn(DB_DSN_MOBO_JIRA,DB_MOBO_JIRA_USERNAME,DB_MOBO_JIRA_PASSWORD);
$sql ="SELECT ";
$sql.="pv.vname AS `version`, ";
$sql.="SUM(CASE ji.issuestatus WHEN 6 THEN 1 WHEN 5 THEN 0.9 END)/COUNT(ji.id)*100 AS `rate` ";
$sql.="FROM ";
$sql.="jiraissue ji ";
$sql.="JOIN ";
$sql.="nodeassociation nn ";
$sql.="ON ji.id = nn.SOURCE_NODE_ID ";
$sql.="JOIN ";
$sql.="projectversion pv ";
$sql.="ON pv.id = nn.SINK_NODE_ID ";
$sql.="WHERE ji.project IN (10100) ";
$sql.="AND pv.PROJECT IN (10100) ";
$sql.="AND nn.SINK_NODE_ENTITY = 'Version' ";
$sql.="AND nn.ASSOCIATION_TYPE = 'IssueVersion' ";
$sql.="AND ji.issuetype = 1 ";
$sql.="GROUP BY `version` ";
$sql.="ORDER BY `version` ";
try{
$st=$conn->prepare($sql);
$st->execute();
$row=$st->fetchAll();
}catch(PDOException $e){
echo "failure:".$e->getMessage();
return false;
}
return $row;
}
}
?>