Print Table

select * from t_prt_document_print order by inserted_timestamp desc

t_prt_printer
PRINTER_ID	PRINTER_PATH	ON_SERVICE	MULTI_TRAY	PRINTER_CODE	ORGAN_ID
2	\\meeting_027\QRCode Print	Y	N		11
t_prt_template_file_multilan
FILE_ID	LANG_ID	DEFAULT_FILE_FORMAT	TEMPLATE_FILE_PATH	GEN_CLASS_NAME	PRINTER_ID	TRAY_NO	DUPLEX_OR_NOT	PRINTER_CODE	TEMPLATE_TYPE	SUPPORT_WATERMARK	XML_SCHEMA
10150	533	7	QRCodeNote.rpt						3	N	qrcode
10151	533	7	QRCode.rpt		2		Y		3	N	qrcode

RI Relase Owner

問題追查

http://210.13.77.92:8115/insurance/gs/servlet/cht.TestAction

select * from t_clm_sys_parameter where para_code='QRCODE_PRINT_PATH'

PARA_CODE	PARA_NAME	PARA_VALUE	PARA_DESC

QRCODE_PRINT_PATH	QRCODE PRINT PATH	//172.25.12.191/msig_r2_tst4/document/xml/qrcode/

移除 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

Excel VBA RegExp Function

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

=CONCATENATE("CLM-",reg(B2, "\d\d\d"))

Excel Macro

Sub delete4857to5236()
'
' delete4857to5236 巨集
'
' 快速鍵: Ctrl+a
'
    Application.Goto Reference:="R4857:R5236"
    Selection.Delete Shift:=xlUp
    Rows("1:1").Select
    Range(Selection, Selection.End(xlDown)).Select
    ActiveWindow.SmallScroll Down:=6
    Rows("1:10000").Select
    Selection.RowHeight = 18.00
    Range("A1").Select
    Selection.End(xlDown).Select
End Sub

drop user & role

drop user gs38ga cascade;
drop user gs38ga_app cascade;
drop user gs38ga_read cascade;
drop role GS38GA_APP_ROLE cascade;
drop role GS38GA_READ_ROLE casacde;