通过使用完整的仓库路径和修订版号构建单独的查询,我能够完成仅SQL的Diff操作 。 有很多改进的余地 (例如解析第一个查询中的信息,而不是运行所有三个查询),但是由于我想将其用于今天的报告,所以我用光了时间。 任何有用的建议,将不胜感激!
我首先使用P4Report并从文件表(符合条件的第1行和第2行)中查询所有信息,然后将其输出到“报告”文本文件中。 最终,这将使我的报告成为Excel的标题。
P4报表查询:
select * from files where RIGHT (FILE,9) = 'chema.sql' and TIME > timestampadd(4, -8, now()) and action in ('add', 'edit', 'integrate') order by time;
然后,我只查询路径和文件名,并将其存储在变量中(第3和4行)。
P4报表查询:
select file from files where RIGHT (FILE,9) = 'chema.sql' and TIME > timestampadd(4, -8, now()) and action in ('add', 'edit', 'integrate') order by time;
最后,我查询了修订版本号,并将其也存储在变量中(第7和8行)。
P4报表查询:
select revision from files where RIGHT (FILE,9) = 'chema.sql' and TIME > timestampadd(4, -8, now()) and action in ('add', 'edit', 'integrate') order by time;
然后,我使用一个foreach循环来创建和执行每个Perforce命令行字符串,并将每个结果附加到“ _Diff_Report”文本文件中。
最后,我从“ _Diff_Report”中删除了所有选项卡(这使我导入Excel时更加整洁)。
$Rpt = '"C:\Program Files\Perforce\P4Report\p4sql.exe" -d ""~"" -i C:\Users\UserName\documents\Queries\DataQ\_Report.txt > C:\Users\UserName\Documents\Queries\Report.txt'
$out = iex "& $Rpt"
$Cmd1 = '"C:\Program Files\Perforce\P4Report\p4sql.exe" -i C:\Users\UserName\Documents\Queries\DataQ\_Files.txt'
$fil = iex "& $Cmd1"
# Debug
# $fil | Out-File C:\Users\UserName\Documents\Queries\_fil_Debug.txt
$Cmd2 = '"C:\Program Files\Perforce\P4Report\p4sql.exe" -i C:\Users\UserName\Documents\Queries\DataQ\_Revisions.txt'
$rev = iex "& $Cmd2"
# Debug
# $rev | Out-File C:\Users\UserName\Documents\Queries\_Rev_Debug.txt
$dt = Get-Date
$dt | Out-File C:\Users\UserName\Documents\Queries\DataQ\_Diff_Report.txt
$f = $fil
$r = $rev
# Debug
# $r | Out-File C:\Users\UserName\Documents\Queries\_R_variable_Debug.txt
$cnt = 2
foreach ($Change in $f)
{
$revision = $r[$cnt] -1
$Cmd3 = '"C:\Program Files\Perforce\p4" diff2 -db '
$Cmd3 = $Cmd3 + '"'
$Cmd3 = $Cmd3 + $f[$cnt]
$Cmd3 = $Cmd3 + '--'
$Cmd3 = $Cmd3 + $revision
$Cmd3 = $Cmd3 + '"'
$Cmd3 = $Cmd3 + ' ' + '"'
$Cmd3 = $Cmd3 + $f[$cnt]
$Cmd3 = $Cmd3 + '--'
$Cmd3 = $Cmd3 + $r[$cnt]
$Cmd3 = $Cmd3 + '"'
$Cmd3
$Diffs = iex "& $Cmd3"
$Diffs | Out-File C:\Users\UserName\Documents\Queries\DataQ\_Diff_Report.txt -Append
$cnt = $cnt + 1
}
$Content = [IO.File]::ReadAllText("C:\Users\UserName\Documents\Queries\DataQ\_Diff_Report.txt")
$Content = $Content -replace "`t",''
Write-Host $Content
[IO.File]::WriteAllText("C:\Users\UserName\Documents\Queries\Diff_Report.txt", $Content)