移除 external/internal defect間不同的columns RemoveDistinctColumns

Function reg(str As String, pattern As String)
    Set regEx = CreateObject("VBScript.RegExp")
    regEx.pattern = pattern
    regEx.Global = True
    regEx.IgnoreCase = True
    Set Matches = regEx.Execute(str)

    If Matches.Count = 0 Then
        reg = "Not Found"
    Else
        reg = Matches(0).Value
    End If

End Function

Sub RemoveDistinctColumns()

    Dim sel As String
    Dim title As String
 
    For i = 1 To 60
        title = Cells.Item(1, i)

       
        If (reg(title, "Artifact ID|Title|Description|Submitted By|Submitted On|Last Modified|Closed|Status|Category|Priority|Assigned To|Reported in Release|Fixed in Release|Estimated Effort|Actual Effort|Planned For|Review Peer|Verifier|Precausation|Injection Cause|Application For|Resolved&Unit Test Detail|Injection Phase|Review Finish Day|Module|Expect Finish Date|Injection Version|Analyze Finish Date|Verify Finish Day|Severity|Defect 提出日期|Rejection Reason|Rejection Reason Details|Req ID|Detected Env|Owner|Owner  Finish Date|Inject By|Client ID|Discover Phase|Responsible Party|Monitoring Status|Dependency Parent|Dependency Children|Item Link") = "Not Found") Then
     
     
            sel = sel + Columns(i).Address + ","
        ElseIf title = "Failed Category" Then
            sel = sel + Columns(i).Address + ","
        End If
     
     
    Next
 
 
 
    sel = Left(sel, Len(sel) - 1)
    Range(sel).Select


    Selection.Delete Shift:=xlToLeft
End Sub

查詢報檢單號所處流程以及在哪個User手裡

select b.name_, e.user_name
  from jbpm_variableinstance a, jbpm_taskinstance b, t_user e
where a.taskinstance_ = b.id_
   and a.stringvalue_ = '14100001029'
   and b.end_ is null
   and b.actorid_ = e.user_id
union all
select b.name_, e.user_name
  from jbpm_variableinstance a,
       jbpm_taskinstance     b,
       t_user                e,
       jbpm_taskactorpool    g
where a.taskinstance_ = b.id_
   and a.stringvalue_ = '14100001029'
   and b.end_ is null
   and g.actorid_ = e.user_id
   and a.taskinstance_ = g.taskinstance_;

TwoDateBetween Lastweek

Function dateBetween(d1 As String, tdate As String) As Boolean
    Dim d_1
    d_1 = CDate(d1)
   
    ' 一週前
    Dim fdate As Date
    fdate = CDate(tdate) - 7
   
    fyear = DatePart("yyyy", fdate)
    fday = DatePart("y", fdate)
    tyear = DatePart("yyyy", CDate(tdate))
    tday = DatePart("y", CDate(tdate))
   
    theYear = DatePart("yyyy", CDate(d1))
    theDay = DatePart("y", CDate(d1))
    dateBetween = False
   
    If (fyear > tyear) Then
        dateBetween = False
    ElseIf (fyear <= tyear) Then
        If (theYear = fyear And theYear = tyear) Then
            If (theDay >= fday And theDay <= tday) Then
                dateBetween = True
            End If
        ElseIf (theYear > fyear And theYear = tyear) Then
        '2014/11/03 < 2015/01/02 < 2015/01/03
            If (theDay <= tday) Then
                dateBetween = True
            End If
       
        ElseIf (theYear = fyear And theYear < tyear) Then
        ' 2014/11/03 < 2014/11/04 < 2015/01/03
            If (theDay >= fday) Then
                dateBetween = True
            End If
        End If
       
    End If
End Function

Function TwoDateBetween(d1 As String, d2 As String, tdate As String) As Boolean

   
    TwoDateBetween = dateBetween(d1, tdate) Or dateBetween(d2, tdate)
   
End Function

Function testDate(d1 As String)

    Dim d As Date
    d = CDate(d1) - 7
    testDate = d
   
End Function

DateBetween

Function dateBetween(d1 As String, fdate As String, tdate As String) As Boolean
    Dim d_1

    d_1 = CDate(d1)
   
    fyear = DatePart("yyyy", CDate(fdate))
    fday = DatePart("y", CDate(fdate))
    tyear = DatePart("yyyy", CDate(tdate))
    tday = DatePart("y", CDate(tdate))
   
    theYear = DatePart("yyyy", CDate(d1))
    theDay = DatePart("y", CDate(d1))
    dateBetween = False
   
    If (fyear > tyear) Then
        dateBetween = False
    ElseIf (fyear <= tyear) Then
        If (theYear = fyear And theYear = tyear) Then
            If (theDay >= fday And theDay <= tday) Then
                dateBetween = True
            End If
        ElseIf (theYear > fyear And theYear = tyear) Then
        '2014/11/03 < 2015/01/02 < 2015/01/03
            If (theDay <= tday) Then
                dateBetween = True
            End If
       
        ElseIf (theYear = fyear And theYear < tyear) Then
        ' 2014/11/03 < 2014/11/04 < 2015/01/03
            If (theDay >= fday) Then
                dateBetween = True
            End If
        End If
       
    End If
End Function

Function TwoDateBetween(d1 As String, d2 As String, fdate As String, tdate As String) As Boolean

   
    TwoDateBetween = dateBetween(d1, fdate, tdate) Or dateBetween(d2, fdate, tdate)
   
End Function