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
|
Sub Macro3()
Dim AnneeEC As Integer
Dim SemaineEC As Integer
Dim shtoto As Worksheet
Dim SemTester As Integer
Dim SemDeb As Integer
'récuperation de la semaine en cours
datedujour = Date
SemaineEC = CInt(Format(datedujour, "ww", 2))
AnneeEC = CInt(Format(datedujour, "yyyy", 2))
SemDeb = SemaineEC - 2
For I = SemDeb To SemaineEC
Set shtoto = Sheets.Add(After:=Sheets(Sheets.Count))
shtoto.Name = "S" & I
Range("A2").Select
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:="ODBC;DSN=mantis;", Destination:=Range("$A$1")).QueryTable
.CommandText = "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')))= '" & AnneeEC & "' AND week (date_format(from_unixtime (date_submitted),get_format(date,'iso')),3) <= '" & AnneeEC & "') OR year (date_format(from_unixtime (date_submitted),get_format(date,'iso'))) < '" & AnneeEC & "')) OR " & _
"((t_bug.status = '90' AND year (date_format(from_unixtime (t_hist.date_modified),get_format(date,'iso'))) = '" & AnneeEC & "' AND week (date_format(from_unixtime (t_hist.date_modified),get_format(date,'iso')),3) > '" & I & "') AND ( (year (date_format(from_unixtime (date_submitted),get_format(date,'iso'))) = '" & I & "' AND week (date_format(from_unixtime (date_submitted),get_format(date,'iso')),3) <= '" & I & "' ) OR year (date_format(from_unixtime (date_submitted),get_format(date,'iso'))) < '" & AnneeEC & "' ) )) " & _
"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:=True
End With
Set shtoto = Nothing
Next
End Sub |
