封装一个根据sql快速生成记录的脚本-SIMATIC IT EBR(Opcenter Execution Pharma)

在PI设计中，经常会遇到需要抓取数据库中的记录，为了快速方便的抓取记录，分享一个封装脚本：

效果预览

脚本编写

脚本内容

可以看到，使用时脚本内容非常简单，具体内容如下：

sql = "select * from E2S_PITEXT_MAN t where t.codefab='OF00000125 '"  
iskey = 1
strHead="标题1,标题2,标题3,标题4,标题5,标题6,标题5,标题6,标题5,标题6,标题5,标题6"
title = "头部标题" 
color = "#1BC85E" 
styletype =2 
const MBMASTERTABLE = "HTML_RECORD"
mAPIScript.callSubScript MBMASTERTABLE,CStr(color),CStr(strHead),cdbl(iskey),CStr(sql),CStr(title),cdbl(styletype)

参数说明

• sql：需要查询的sql语句，可以根据需求拼接
• iskey：是否显示序号：NO
• strHead：列表标题，顺序与sql查询出的结果列顺序一致
• title：整体标题
• color ： 颜色风格
• styletype ：样式，1或者2，目前就支持两种样式

注意事项

我们在这里调用了另外一个脚本，html_record,需要注意的是，必须将调用的脚本link进去：

封装脚本内容

htmlrecord CStr(insSQL),Clng(insISKEY),CStr(insHEAD),CStr(insTITLE),CStr(insCOLOR),Clng(insTYPE)  
sub htmlrecord(ByVal sql,ByVal iskey,ByVal strHead,ByVal title,ByVal color,ByVal styletype)
dim rst_h,str_htmlr,key_r,key_i,max_head,arrayHEAD
arrayHEAD=split(strHead,",") 
key_r = 1 
max_head = Ubound(arrayHEAD)

set rst_h = mglob.DB.conData.Execute(sql)  
str_htmlr="" 
if styletype=1 then
str_htmlr = str_htmlr & "<style>.htmltable table{border-collapse: collapse;background: #fff;width:100%;border:none !important;}" 
str_htmlr = str_htmlr & ".htmltable th{font-size: 16px !important; font-weight:bold;color: "&color&";background:none;padding: 5px 10px;border-bottom: 2px solid "&color&";}"
str_htmlr = str_htmlr & ".htmltable td{border:none !important;border-bottom: 1px solid #ccc !important;color: #222222;padding: 6px 8px;text-align: center;}"
str_htmlr = str_htmlr & ".htmltable tbody tr:hover td{ color: #222222;}</style> "  
else
str_htmlr = str_htmlr & "<style>.htmltable table{border-collapse: collapse;background: #fff;width:100%;border:none !important;}" 
str_htmlr = str_htmlr & ".htmltable th{font-size: 16px !important;font-weight: bold;color: #fff;padding: 5px 10px;background: "&color&";border: 1px solid #ccc;}"
str_htmlr = str_htmlr & ".htmltable td{color: #222222;padding: 6px 8px;text-align: center;border: 1px solid #ccc;}"
str_htmlr = str_htmlr & ".htmltable tbody tr:hover td{ color: #222222;}</style> " 
end if
str_htmlr = str_htmlr & "<div class='htmltable'><p><b style='border-left:4px solid "&color&";padding-left:5px;'>"&title&"</b></p>" 
str_htmlr = str_htmlr & "<Table><thead><tr>"   
if iskey = 1 then
str_htmlr = str_htmlr & "<th>NO</th>"  
end if 
for key_i=0 to max_head    
str_htmlr = str_htmlr & "<th>"&arrayHEAD(key_i)&"</th>"  
next 
str_htmlr = str_htmlr & "</tr></thead><tbody>"
    If rst_h.RecordCount > 0 Then  
    do while rst_h.EOF = False 
    str_htmlr = str_htmlr & "<tr>" 
    if iskey = 1 then
    str_htmlr = str_htmlr & "<td>"&key_r&"</td>" 
    end if
    for key_i=0 to max_head    
    str_htmlr = str_htmlr & "<td>"&rst_h(key_i)&"</td>"  
    next 
    str_htmlr = str_htmlr & "</tr>"  
    key_r=key_r+1
    rst_h.MoveNext     
    loop 
    else
        if iskey = 1 then
        str_htmlr = str_htmlr & "<tr><td colspan='"&max_head+2&"'>暂无记录</td></tr>"
        else
        str_htmlr = str_htmlr & "<tr><td colspan='"&max_head+1&"'>暂无记录</td></tr>"
        end if
    end if
    str_htmlr =str_htmlr & "</tbody></Table></div><br>" 
    'inputbox "1","1", str_htmlr
mRecordText.WriteHTML str_htmlr   
end sub