import win32com.client
import traceback
import time


# 获取运行时的数据库和计算机名称
def getDatasourceNameRT():
    runTime = win32com.client.Dispatch(r'CCHMIRuntime.HMIRuntime')
    catalog = runTime.Tags("@DatasourceNameRT").Read()  # 获取数据库名称
    # dataSource = runTime.Tags("@LocalMachineName").Read()  # 获取计算机名称
    return catalog


# 获取变量记录的变量名
def getArchiveTagNames():
    tagNames = {}
    try:
        catalog = getDatasourceNameRT()
        conn = win32com.client.Dispatch(r'ADODB.Connection')
        # 因为数据库无账号密码（使用Windows NT集成安全设置），所以必须设定Integrated Security=SSPI; 连通性手册有说明
        DSN = "Provider=SQLOLEDB.1;Data Source=37IK1BOAKVD0VE6\WINCC;Initial Catalog={0};Integrated Security=SSPI;".format(
            catalog)
        # print(DSN)
        conn.ConnectionString = DSN
        conn.CursorLocation = 3
        conn.Open()

        oRs = win32com.client.Dispatch(r'ADODB.Recordset')
        sSql = "SELECT [ValueID], [ValueName] FROM[{0}].[dbo].[Archive]".format(catalog)
        # print(sSql)
        oRs.Open(sSql, conn, 1, 1)
        # print(oRs.RecordCount)

        if not oRs.EOF:
            oRs.MoveFirst()
            while not oRs.EOF:
                # for i in range(len(oRs.Fields)):
                #     print(oRs.Fields(i).Name, "=", oRs.Fields(i).Value)
                tagNames[int(oRs.Fields(0).Value)] = oRs.Fields(1).Value
                oRs.MoveNext()
        oRs.Close()
        conn.Close()
    except:
        traceback.print_exc()
    finally:
        return tagNames


# 获取变量记录的值
def getTagValue():
    try:
        # 获取数据库名
        catalog = getDatasourceNameRT()
        conn = win32com.client.Dispatch(r'ADODB.Connection')
        DSN = "Provider=WinCCOLEDBProvider.1;Catalog={0};Data Source=.\WinCC".format(catalog)
        conn.ConnectionString = DSN
        conn.CursorLocation = 3
        conn.Open()

        oRs = win32com.client.Dispatch(r'ADODB.Recordset')

        # 获取变量名称
        tagNames = getArchiveTagNames()

        # 方法1：变量名称
        # s = ["'%s'" % name for name in list(tagNames.values())]
        # tags = ";".join(s)
        # sSql = "TAG:R, ({0}), 2019-1-6 00:00:00.000,2019-1-7 00:00:00.000".format(tags)

        # 方法2：变量ID
        sSql = "TAG:R, ('9';'14'), '2019-1-6 00:00:00.000','2019-1-7 00:00:00.000'"

        print(sSql)
        t0 = time.time()
        oRs.Open(sSql, conn)
        print(oRs.RecordCount, oRs.PageSize, oRs.PageCount, oRs.CacheSize)

        i = 0
        if not oRs.EOF:
            oRs.MoveFirst()
            while not oRs.EOF:
                # 方法1
                a = oRs.GetRows(100000)
                for j in range(len(a[1])):
                    print(i * 100000 + j, a[1][j], tagNames[a[0][j]], a[3][j])
                i += 1

                # 方法2
                # print(oRs.GetString())

                # 方法3
                # for i in range(len(oRs.Fields)):
                #     print(oRs.Fields(i).Name, "=", oRs.Fields(i).Value)
                # oRs.MoveNext()
    except:
        traceback.print_exc()
    finally:
        print(time.time() - t0)


if __name__ == '__main__':
    getTagValue()