|
首页
>> 配置管理
>> IBM Rational ClearQuest / ClearDDTS/TestManager
|
| 使用脚本轻松导出本周纪录 |
| 作者 yunshan 查看 6283 发表时间 2007/1/29 16:02 【论坛浏览】 |
使用脚本轻松导出本周纪录dkti
dkti
因为CQ中没有关于本周纪录查询的const，如TODAY，TOMORROW，YESTODAY等，也因此给很多人带来了不少麻烦。dkti
既然不能在客户端中直接建立这种查询（有人提过在客户端中通过使用SQL来实现，但是这种SQL可能很复杂，并不是每个人都能掌握的），所以本人就想到了使用外部脚本来实现。dkti
dkti
思想：通过运行一个.vbs脚本，来查询本周纪录，并导出到Excel中。dkti
dkti
实现：以下是实现这一思想的脚本，把它save成一个.vbs文件，然后把其中的session.Logon部分根据自己的实际情况来做适当的更改，保存后，任何时候一运行，就能把本周（一般是星期一到当前系统时间）纪录保存到一个excel文件中，非常方便。dkti
dkti
' --------------------------------------------------------dkti
' Script Name: Exprot_Weekly_Defects.vbsdkti
' Author: yunshandkti
' Create Date: 2007-1-28dkti
'---------------------------------------------------------dkti
dkti
' Declare the global constantdkti
Public Const SUCCESS = 1dkti
Public Const AD_BOOL_OP_AND = 1dkti
Public Const AD_COMP_OP_EQ = 1dkti
dkti
Dim curDatedkti
Dim curWeekdkti
Dim intervaldkti
Dim strDatedkti
dkti
' Get the current date and compute the strDatedkti
curWeek = DatePart("w", Now)dkti
interval = (curWeek + 6) Mod 7dkti
If interval = 0 Thendkti
interval = 7dkti
End Ifdkti
interval = interval - 1dkti
strDate = DateAdd("d", -interval, Date)dkti
dkti
' The start date of this week is from monday, time initial is 00:00:00dkti
strDate = strDate & " 00:00:00"dkti
dkti
Dim sessiondkti
Dim resultsetdkti
dkti
' Login to the destination databasedkti
Set session = CreateObject("CLEARQUEST.SESSION")dkti
session.UserLogon "admin", "", "cdi", AD_PRIVATE_SESSION, "cdi"dkti
dkti
' Build Query On defectdkti
Set resultset = session.BuildSQLQuery("select T1.id,T1.headline,T7.name,T1.priority, " &_dkti
"T2.login_name,T1.submit_date from Defect T1,statedef T7,users T2 where T1.state = T7.id " &_dkti
"and T1.owner = T2.dbid and Submit_Date between"&_ dkti
" #"& strDate &"# and #"& curDate &"#")dkti
dkti
'resultset.EnableRecordCountdkti
resultset.Executedkti
dkti
Dim xlsAppdkti
Dim newBookdkti
Dim newSheetdkti
dkti
' Create Excel App and set property for the new filedkti
Set xlsApp = CreateObject("Excel.Application")dkti
set newBook = xlsApp.Workbooks.Adddkti
with newBookdkti
.Title = "All this weeks defect"dkti
.Subject = "ClearQuest"dkti
.Activatedkti
End Withdkti
dkti
' work with sheet1dkti
Set newSheet = newBook.Worksheets("Sheet1")dkti
newSheet.Visible = Truedkti
newSheet.Name = "Weekly Defects"dkti
dkti
' set column titledkti
newSheet.Range("A1:F1").Value = Array("ID","Headline","State","Priority","Owner","Submit Date")dkti
newSheet.Range("A1:F1").Font.Bold = Truedkti
dkti
' set values for destination cellsdkti
Dim idkti
i = 2dkti
Do While resultset.MoveNext = SUCCESSdkti
newSheet.Cells(i,1).Value = resultset.GetColumnValue(1)dkti
newSheet.Cells(i,2).Value = resultset.GetColumnValue(2)dkti
newSheet.Cells(i,3).Value = resultset.GetColumnValue(3)dkti
newSheet.Cells(i,4).Value = resultset.GetColumnValue(4)dkti
newSheet.Cells(i,5).Value = resultset.GetColumnValue(5)dkti
newSheet.Cells(i,6).Value = resultset.GetColumnValue(6)dkti
i = i + 1dkti
Loopdkti
dkti
' Save changes to the exceldkti
newBook.SaveAs("C:\WeeklyDefects.xls")dkti
dkti
' release the objectsdkti
Set newSheet = Nothingdkti
newBook.Closedkti
Set newBook = Nothingdkti
xlsApp.Quitdkti
Set xlsApp = Nothingdkti
Set resultset = Nothingdkti
Set session = Nothingdkti
MsgBox "Finish exporting records!"dkti
dkti
' 大家可以在这个脚本的基础上作适当的改动，以扩展它的功能。dkti
dkti
'==================================================================dkti
' 对以上脚本的升级dkti
' Author: yunshandkti
' Description: 改动部分用黑体标出dkti
'==================================================================dkti
Public Const SUCCESS = 1dkti
Public Const AD_BOOL_OP_AND = 1dkti
Public Const AD_COMP_OP_EQ = 1dkti
Public Const AD_COMP_OP_BETWEEN = 9dkti
dkti
Dim curWeekdkti
Dim intervaldkti
Dim strDatedkti
dkti
' Get the current date and compute the strDatedkti
curWeek = DatePart("w", Now)dkti
interval = (curWeek + 6) Mod 7dkti
If interval = 0 Thendkti
interval = 7dkti
End Ifdkti
interval = interval - 1dkti
strDate = DateAdd("d", -interval, Date)dkti
strDate = strDate & " 00:00:00"dkti
dkti
Dim sessiondkti
Dim qryObjdkti
Dim filterObjdkti
Dim resultsetdkti
Dim dateRangedkti
ReDim dateRange(1)dkti
dateRange(0) = strDatedkti
' 修正了一个小错误，把dateRange(1) = Now 改成了dateRange(1) = Cstr(Now)，否则运行会出错。dkti
dateRange(1) = Cstr(Now)dkti
dkti
dkti
' Login to the destination databasedkti
Set session = CreateObject("CLEARQUEST.SESSION")dkti
session.UserLogon "admin", "", "productDB", AD_PRIVATE_SESSION, "masterDB"dkti
dkti
' Build Query On defectdkti
Set qryObj = session.BuildQuery("defect")dkti
qryObj.BuildField("id")dkti
qryObj.BuildField("headline")dkti
qryObj.BuildField("State")dkti
qryObj.BuildField("priority")dkti
qryObj.BuildField("owner")dkti
qryObj.BuildField("Submit_Date")dkti
Set node = qryObj.BuildFilterOperator(AD_BOOL_OP_AND)dkti
node.BuildFilter "Submit_Date",AD_COMP_OP_BETWEEN, dateRangedkti
dkti
Set resultset = session.BuildResultSet(qryObj)dkti
dkti
' resultset.EnableRecordCountdkti
resultset.Executedkti
dkti
Dim xlsAppdkti
Dim newBookdkti
Dim newSheetdkti
dkti
' Create Excel App and set property for the new filedkti
Set xlsApp = CreateObject("Excel.Application")dkti
set newBook = xlsApp.Workbooks.Adddkti
with newBookdkti
.Title = "All this weeks defect"dkti
.Subject = "ClearQuest"dkti
.Activatedkti
End Withdkti
dkti
' work with sheet1dkti
Set newSheet = newBook.Worksheets("Sheet1")dkti
newSheet.Visible = Truedkti
newSheet.Name = "Weekly Defects"dkti
dkti
' set column titledkti
With newSheet.Range("A1:F1")dkti
.Value = Array("ID","Headline","State","Priority","Owner","Submit Date")dkti
.Font.Bold = Truedkti
.Font.Color = vbWhitedkti
.Interior.ColorIndex = 1dkti
End Withdkti
dkti
' set values for destination cellsdkti
Dim idkti
i = 2dkti
Do While resultset.MoveNext = SUCCESSdkti
newSheet.Cells(i,1).Value = resultset.GetColumnValue(1)dkti
newSheet.Cells(i,2).Value = resultset.GetColumnValue(2)dkti
newSheet.Cells(i,3).Value = resultset.GetColumnValue(3)dkti
newSheet.Cells(i,4).Value = resultset.GetColumnValue(4)dkti
newSheet.Cells(i,5).Value = resultset.GetColumnValue(5)dkti
newSheet.Cells(i,6).Value = resultset.GetColumnValue(6)dkti
i = i + 1dkti
Loopdkti
dkti
newSheet.Columns("A:F").AutoFitdkti
newBook.SaveAs("C:\WeeklyDefects.xls")dkti
dkti
newBook.Closedkti
Set newBook = Nothingdkti
xlsApp.Quitdkti
Set xlsApp = Nothingdkti
Set resultset = Nothingdkti
Set session = Nothingdkti
dkti
[ 本帖最后由 yunshan 于 2007-10-24 18:48 编辑 ] |
| 序号 |
评论者 |
共有评论 30 【论坛浏览】 【发表评论】 |
评论时间 |
|
| 11 |
ygh1999 |
("select T1.id,T1.headline,T7.name,T1.priority, " &_
"T2.login_name,T1.submit_date from Defect T1,statedef T7,users T2 where T1.state = T7.id " &_
"and T1.owner = T2.dbid and Submit_Date between"&_
" #"& strDate &"# and #"& curDate &"#")
这个语句有问题，老报错，后来我修改了，但是这个Submit_Date between语句还是有问题，查找不到数据，请指点
Set resultset = session.BuildSQLQuery("select T1.id,T1.headline,T7.name,T1.priority,T2.login_name,T1.submit_date from Defect T1,statedef T7,users T2 where T1.state = T7.id and T1.owner = T2.dbid and Submit_Date between convert(varchar(10),'"+strDate+"',121) and convert(varchar(10),'"+curDate+"',121) ") |
2007/2/13 09:54 |
|
| 12 |
yunshan |
回复 #12 ygh1999 的帖子
不知道上面的那个语句在你那边提示什么错误，但是我想你可以通过msgbox来把其中的变量strDate和curDate的值在执行的时候先显示出来，看看是否都是正确的，还有在使用我上面的脚本的时候，注意脚本中的字段名成与实际你的定义的是否一致，否则，就要对代码进行更改。
还有建议使用我后来写的那个第二个升级脚本，因为这个里面没有涉及到SQL语句，不容易出错~ |
2007/2/13 12:26 |
|
| 13 |
gjhna |
如果想在文件名WeeklyDefects.xls中加入当前日期，即生成的文件名为WeeklyDefects20070314.xls的格式，应该怎么修改呢？ |
2007/3/14 13:31 |
|
| 14 |
iltest |
我试了一把,好像没有成功.老是报错.
我就只修改了下面这句:
session.UserLogon "admin", "", "productDB", AD_PRIVATE_SESSION, "masterDB"
其中productDB改成我的user database:testQ
masterDB改成了maintance tool中的master数据库的名称:testC
但是一运行脚本就提示说:"the database "MASTR"belonging to master database"is an invalid name.Enter the correct name of a ClearQuest user database.""
请问这是什么原因啊? |
2007/3/14 14:38 |
|
| 15 |
ljs53 |
"masterDB" 这个指的是连接名
例如下图中的Connection1 |
2007/3/14 15:56 |
|
| 16 |
iltest |
就是因为把连接名改成数据库名了,所以才报错.现在已经可以导成功了
谢谢ljs53 |
2007/3/14 16:09 |
|
| 17 |
gjhna |
使用这个脚本后只能导出星期一提交的CR,请问是什么原因呢？ |
2007/3/15 13:09 |
|
| 18 |
ljs53 |
回复 #18 gjhna 的帖子
突然发现我的也是呀
以前没有注意过
因为一直没有怎么用 是不是和时间有关系呢 |
2007/3/15 14:35 |
|
| 19 |
gjhna |
回复 #19 ljs53 的帖子
不知道是不是和系统的时间设置有关系，我的CQ中的Submit_Date的日期和时间都是引用的控制面板的短日期和时间格式，但是改成长日期后仍然只能导出周一的记录，看脚本实在是看不出问题在哪。。。。 |
2007/3/15 14:41 |
|
| 20 |
yunshan |
更改脚本，
在生成的WeeklyDefects.xls中加入日期，
把脚本中的这一行
newBook.SaveAs("C:\WeeklyDefects.xls")
改成
Dim MyDate
MyDate = Date
newBook.SaveAs("C:\WeeklyDefects_" & DatePart("m",MyDate) & "_" & DatePart("d",MyDate) & "_" & DatePart("yyyy",MyDate) & ".xls")
就可以了 |
2007/3/16 11:11 |
共有评论数 30 每页显示 10
|
|
