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
|
Sub Macro3()
Range("A2").Select
Sheets.Add After:=Sheets(Sheets.Count)
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:="ODBC;DSN=mantis;" _
, Destination:=Range("$A$1")).QueryTable
.CommandText = Array( _
"SELECT (select T1.value from mantis_custom_field_string_table T1 where t_bug.id = T1.bug_id and field_id = 7), t_custf.value, (select case when SUBSTRING(T2.value,2,2) = 'P0' then 'P0' when SUBSTRING(T2.value,2,2)='P1' then 'P1' when SUBSTRING(T2.value,2,2) = 'P2' then 'P2'else 'NC' end from mantis_custom_field_string_table T2 where t_bug.id = T2.bug_id and field_id = 4, count(t_bug.id)" _
, _
"FROM mantis_bug_table t_bug inner join mantis_project_table t_proj on t_bug.project_id=t_proj.id left outer join mantis_custom_field_string_table t_custf on t_bug.id=t_custf.bug_id and t_custf.field_id='6' left outer join mantis_user_table t_user on t_bug.reporter_id=t_user.id inner join mantis_category_table t_cat on t_bug.category_id=t_cat.id left outer join mantis_bug_history_table t_hist on t_bug.id = t_hist.bug_id and (t_hist.new_value='90' and t_hist.field_name = 'status')" _
, _
"WHERE t_proj.name = 'Mag21_Fly' AND ((t_bug.status <> '90' ANd ((year (date_format(from_unixtime (date_submitted),get_format(date,'iso')))= '2013' AND week (date_format(from_unixtime (date_submitted),get_format(date,'iso')),3) <= '2013') OR year (date_format(from_unixtime (date_submitted),get_format(date,'iso'))) < '2013')) OR" _
, _
"((t_bug.status = '90' AND year (date_format(from_unixtime (t_hist.date_modified),get_format(date,'iso'))) = '2013' AND week (date_format(from_unixtime (t_hist.date_modified),get_format(date,'iso')),3) > '33') AND ( (year (date_format(from_unixtime (date_submitted),get_format(date,'iso'))) = '33' AND week (date_format(from_unixtime (date_submitted),get_format(date,'iso')),3) <= '33' ) OR year (date_format(from_unixtime (date_submitted),get_format(date,'iso'))) < '2013' ) )) " _
, _
"group by (select T1.value from mantis_custom_field_string_table T1 where t_bug.id = T1.bug_id and field_id = 7) ,t_custf.value , (select case when SUBSTRING(T2.value,2,2) = 'P0' then 'P0' when SUBSTRING(T2.value,2,2) = 'P1' then 'P1' when SUBSTRING(T2.value,2,2) = 'P2' then 'P2' else 'NC' end from mantis_custom_field_string_table T2 where t_bug.id = T2.bug_id and field_id = 4)" _
)
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
End Sub |
Partager