scbc_repos/ssat_sdk/action/Executor.py

#-*- coding:utf-8 -*-
import os, sys, time
import xlrd
import xlwt
import sqlite3
import json
from ssat_sdk.picture.feature_detect import FeatureDetect
from ssat_sdk.utils import LoggingUtil
from ssat_sdk.picture import image_util 

class CExcelData():
    def __init__(self):
        # 编号;
        self.num = 0
        # 类型;
        self.type = ''
        # 控件id;
        self.ctrlid = ''
        # 类型描述;
        self.desc = ''
        # 类型事件;
        self.event = ''
        # 类型参数;
        self.params = ''
        # 类型结果;
        self.typeresult = ''
        # 事件结果;
        self.eventresult = ''
    
    def getdata(self, data):
        if data is not None:
            self.num, self.type, self.ctrlid, self.desc, self.event, self.params, self.typeresult, self.eventresult = tuple(data)
    #end-fun

    def tolist(self):
        return [self.num, self.type, self.ctrlid, self.desc, self.event, self.params, self.typeresult, self.eventresult]
    #end-fun

class CImgView():
    def __init__(self):
        # 图元id;
        self.id = ''
        # 图元名称;
        self.name = ''
        # 图元类型：按钮、滑块
        self.type = ''
        # 图元领空区域;
        self.airspace = []
        # 图元未选时图标区域;
        self.iconarea = []
        # 图元未选时文本区域;
        self.textarea = []
        # 图元未选时文本内容;
        self.textcontent = ''
        # 图元未选时图标内容：使用路径;
        self.iconcontent = ''
        # 图元选中时图标区域;
        self.iconarea2 = []
        # 图元选中时文本区域；
        self.textarea2 = []
        # 图元选中时的文件内容;
        self.textcontent2 = ''
        # 图元选中时的图标内容;
        self.iconcontent2 = ''
        # 展开键;
        self.openkey = ''
        # 返回键;
        self.returnkey = ''
        # 弹出键;
        self.popkey = ''
        # 图元所属图区id;
        self.areaid = 0
        # 图元关联图区id;
        self.nextareaid = 0
    
    def getdata(self,data):
        self.id = data[0]
        self.name = data[1]
        self.type = data[2]
        self.airspace = [] if data[3] is None else json.loads(data[3])
        self.iconarea = [] if data[4] is None else json.loads(data[4])
        self.textarea = [] if data[5] is None else json.loads(data[5])
        self.textcontent = data[6]
        self.iconcontent = data[7]
        self.iconarea2 = data[8]
        self.textarea2 = data[9]
        self.textcontent2 = data[10]
        self.iconcontent2 = data[11]
        self.openkey = data[12]
        self.returnkey = data[13]
        self.popkey = data[14]
        self.areaid = data[15]
        self.nextareaid = data[16]

class CImgArea():
    def __init__(self):
        self.id = ''
        self.name = ''
        self.isroot = False
        self.popkey = ''
        self.returnkey = ''
        self.examplepic = ''
        self.coodinate = []
        self.bgproperty = ''
        self.uidtype = ''
        self.uidcontent = ''
        self.uidarea = []
        self.layouttype = ''
        self.direction = ''
        self.method = ''
        self.refcoordFocusArea = []
        self.refcoordIconArea = []
        self.refcoordTextArea = []
        self.methodParam = {}
        self.layoutParam = {}
        self.ocrParam = []
    
    def getdata(self, data):
        self.id = data[0]
        self.name = data[1]
        self.isroot = data[2]
        self.popkey = data[3]
        self.returnkey = data[4]
        self.examplepic = data[5]
        self.coodinate = [] if data[6] is None else json.loads(data[6])
        self.bgproperty = data[7]
        self.uidtype = data[8]
        self.uidcontent = data[9]
        self.uidarea = data[10]
        self.layouttype = data[11]
        self.direction = data[12]
        self.method = data[13]
        self.refcoordFocusArea = [] if data[14] is None else json.loads(data[14])
        self.refcoordIconArea = [] if data[15] is None else json.loads(data[15])
        self.refcoordTextArea = [] if data[16] is None else json.loads(data[16])
        self.methodParam = {} if data[17] is None else json.loads(data[17])
        self.layoutParam = {} if data[18] is None else json.loads(data[18])
        self.ocrParam = [] if data[19] is None else json.loads(data[19])

    #end-fun

class CParameters():
    def __init__(self):
        self.id = 0
        self.areaid = 0
        self.method = ''
        self.params = ''
    
    def getdata(self,data):
        self.id = data[0]
        self.areaid = data[1]
        self.method = data[2]
        self.params = data[3]
        if self.method == u'全局二值化':
            print(u'全局二值化')
        elif self.method == u'局部二值化':
            pass
        elif self.method == u'方差二值化':
            pass
        elif self.method == u'模板匹配':
            pass
        elif self.method == u'形状匹配':
            pass
    #end-fun

class CExecutor():
    def __init__(self, xls_path, db_path):
        # db连接对象;
        self.conn = None
        # 游标;
        self.cursor = None
        # excel路径;
        self.xls_path = xls_path
        # db路径;
        self.db_path = db_path
        # excel数据;
        self.xls_data = []
        # 识别类对象;
        self.feature = FeatureDetect()
    #end-fun
    
    def read_excel(self, path = None):
        if path is None:
            path = self.xls_path
        #end-if
        # 打开文件;
        wb = xlrd.open_workbook(filename=path)
        if wb is None:
            return
        #通过索引获取表格;
        sheet1 = wb.sheet_by_index(0)
        for i in range(1, sheet1.nrows):
            # 获取行内容;
            rows = sheet1.row_values(i)
            data = CExcelData()
            data.getdata(rows)
            self.xls_data.append(data)
        #end-for
    #end-fun

    def get_conn(self, path = None):
        if path is None:
            path = self.db_path
        #end-if
        if os.path.exists(path) and os.path.isfile(path) :
            self.conn = sqlite3.connect(path)
        #end-if
    #end-fun

    def get_cursor(self) :
        if self.conn is not None:
            self.cursor = self.conn.cursor()
        else:
            self.cursor = self.get_conn().cursor()
    #end-fun

    def fetchone(self, sql, data) :
        if self.conn is None:
            return
        if sql is not None and sql != '' :
            if data is not None :
                #Do this instead
                d = (data, )
                cu = self.cursor
                cu.execute(sql, d)
                rec = cu.fetchall()
                return rec
            else:
                print('the [{}] equal None!'.format(data))
                return None
        else:
            print('the [{}] is empty or equal None!'.format(sql))
            return None
    #end-fun

    def getImgViewData(self,id):
        sql = "SELECT ImgView.Id,"\
        "ImgView.Name,"\
        "ImgView.Type,"\
        "ImgView.AirspaceArea,"\
        "ImgView.IconArea,"\
        "ImgView.TextArea,"\
        "ImgView.TextContent,"\
        "ImgView.IconContent,"\
        "ImgView.IconArea2,"\
        "ImgView.TextArea2,"\
        "ImgView.TextContent2,"\
        "ImgView.IconContent2,"\
        "ImgView.OpenKey,"\
        "ImgView.ReturnKey,"\
        "ImgView.PopKey,"\
        "ImgView.AreaId,"\
        "ImgView.NextAreaId "\
        "FROM ImgView WHERE id = ?"
        # 获取记录集;
        record = self.fetchone(sql, id) 
        data = CImgView()
        if record is not None:
            if len(record) > 0:
                data.getdata(record[0])
                return data
        return None
    #end-fun

    def getImgAreaData(self, id):
        sql = "SELECT ImgArea.Id,"\
        "ImgArea.Name,"\
        "ImgArea.IsRoot,"\
        "ImgArea.PopKey,"\
        "ImgArea.ReturnKey,"\
        "ImgArea.ExamplePicture,"\
        "ImgArea.Coordinate,"\
        "ImgArea.BackgroundProperties,"\
        "ImgArea.UIdType,"\
        "ImgArea.UIdContent,"\
        "ImgArea.UIdArea,"\
        "ImgArea.LayoutType,"\
        "ImgArea.Direction,"\
        "ImgArea.Method,"\
        "ImgArea.refcoordFocusArea,"\
        "ImgArea.refcoordIconArea,"\
        "ImgArea.refcoordTextArea,"\
        "ImgArea.MethodParam,"\
        "ImgArea.LayoutParam,"\
        "ImgArea.OCRParam "\
        "FROM ImgArea WHERE id = ?"
        # 获取记录集;
        record = self.fetchone(sql, id) 
        data = CImgArea()
        if record is not None:
            if len(record) > 0:
                data.getdata(record[0])
                return data
        return None
    #end-fun

    def getParameters(self, areaid):
        sql = "SELECT Parameter.Id,"\
        "Parameter.AreaId,"\
        "Parameter.Method,"\
        "Parameter.Parameters "\
        "FROM Parameter WHERE AreaId = ?"
        # 获取记录集;
        record = self.fetchone(sql, areaid) 
        data = CParameters()
        if record is not None:
            if len(record) > 0:
                data.getdata(record[0])
                return data
        return None
    #end-fun

    # 1、找到加返回True,否则False
    # 2、返回当前焦点坐标;
    def doVerifyOnTargetCtrl(self, pel_data, map_data):
        # 截图，原图路径;
        screenshot = os.path.join(LoggingUtil.getCaseRunLogDirPath(), "TVShot_" + time.strftime("%Y%m%d%H%M%S", time.localtime()) + ".png")
        self.feature.vp.takePicture(screenshot)

        # 根据方法分类执行;
        if map_data.method == u"全局二值化":
            contour = self.feature.getGrayBinaryContour(screenshot, 
                                                        map_data.coodinate, 
                                                        map_data.methodParam['gray'], 
                                                        255, 
                                                        map_data.methodParam['inside'], 
                                                        map_data.methodParam['min-peri'],
                                                        map_data.methodParam['max-peri'],
                                                        map_data.methodParam['min-area'],
                                                        map_data.methodParam['max-area'],)
            # 是否找到轮廓;
            if contour is None:
                return False, [], screenshot
            
            # 获取文本坐标;
            textbox = self.feature.getObjAbsoluteBox(contour, map_data.refcoordFocusArea, map_data.refcoordTextArea)
            # ocr识别;
            ocrImgPath = os.path.join(LoggingUtil.getCaseRunLogDirPath(), "OCR_" + time.strftime("%Y%m%d%H%M%S", time.localtime()) + ".png")
            image_util.saveCropPic(screenshot, ocrImgPath, textbox)
            result, text = self.feature.OCR.findPicStr(pel_data.textcontent, ocrImgPath, map_data.ocrParam[0], map_data.ocrParam[1], map_data.ocrParam[2])
            # 返回结果;
            return result, contour, screenshot
        elif map_data.method == u"局部二值化":
            pass
        elif map_data.method == u"方差二值化":
            pass
        elif map_data.method == u"模板匹配":
            # 模板匹配函数需要改造;
            setting = {'method': 5, 'colorType': 0, 'thresholdVal': 0, 'thresholdMaxVal': 255, 'matchVal': map_data.methodParam['match'] }
            match_result = self.feature.matchSingleImage(screenshot, map_data.coodinate, map_data.methodParam['tempdir'], setting)
            if match_result is None:
                return False, [], screenshot
            else:
                # 获取文本坐标;
                textbox = self.feature.getObjAbsoluteBox(match_result['coordinate'], map_data.refcoordFocusArea, map_data.refcoordTextArea)
                # ocr识别;
                ocrImgPath = os.path.join(LoggingUtil.getCaseRunLogDirPath(), "OCR_" + time.strftime("%Y%m%d%H%M%S", time.localtime()) + ".png")
                image_util.saveCropPic(screenshot, ocrImgPath, textbox)
                result, text = self.feature.OCR.findPicStr(pel_data.textcontent, ocrImgPath, map_data.ocrParam[0], map_data.ocrParam[1], map_data.ocrParam[2])
                # 返回结果;
                return result, match_result['coordinate'], screenshot
        elif map_data.method == u"形状匹配":
            pass
        # 不存在的方法,直接返回False;
        return False, [], screenshot
    #end-fun

    # 判断是否在目标图区上;
    # 根据唯一标识内容来判断;
    def doVerifyOnTargetMapArea(self, map_data):
        result, coodr = False, []
        # 截图，原图路径;
        screenshot = os.path.join(LoggingUtil.getCaseRunLogDirPath(), "TVShot_" + time.strftime("%Y%m%d%H%M%S", time.localtime()) + ".png")
        self.feature.vp.takePicture(screenshot)
        if map_data.uidtype == u"图标":
            # 模板匹配函数需要改造;
            setting = {'method': 5, 'colorType': 0, 'thresholdVal': 0, 'thresholdMaxVal': 255, 'matchVal': map_data.methodParam['match'] }
            match_result = self.feature.matchSingleImage(screenshot, map_data.coodinate, map_data.methodParam['tempdir'], setting)
            if match_result is None:
                return False, [], screenshot
            else:
                return True, match_result['coordinate'], screenshot
        elif map_data.uidtype == u"文本":
            pass
        elif map_data.uidtype == u"颜色":
            pass
        elif map_data.uidtype == u"轮廓":
            pass
        elif map_data.uidtype == u"形状":
            pass
        elif map_data.uidtype == u"":
            pass 
        # 返回结果;
        return result, coodr, screenshot

    # 执行事件;
    def doCtrlEvent(self, xls_data, pel_data, map_data):
        # 执行前截图分析;
        screenshot = os.path.join(LoggingUtil.getCaseRunLogDirPath(), "TVShot_" + time.strftime("%Y%m%d%H%M%S", time.localtime()) + ".png")
        self.feature.vp.takePicture(screenshot)
        # 类型判断;
        if pel_data.type == u"滑块":
            if xls_data.event == u"编辑":
                if map_data.layouttype == u"图独":
                    # 获取文本坐标;
                    textbox = self.feature.getObjAbsoluteBox(map_data.coodinate, map_data.refcoordFocusArea, map_data.refcoordTextArea)
                    # ocr识别;
                    ocrImgPath = os.path.join(LoggingUtil.getCaseRunLogDirPath(), "OCR_" + time.strftime("%Y%m%d%H%M%S", time.localtime()) + ".png")
                    image_util.saveCropPic(screenshot, ocrImgPath, textbox)
                    text = self.feature.OCR.getStrWithImgProcess(ocrImgPath, map_data.ocrParam[2], map_data.ocrParam[0], map_data.ocrParam[1])
                    if text is not None and text is not '':
                        count = int(xls_data.params) - int(text) 
                        if count > 0:
                            self.feature.redRat3.sendKey('right', count, 0.1)
                        elif count < 0:
                            self.feature.redRat3.sendKey('left', abs(count), 0.1)

                        # 执行后截图分析;
                        screenshot = os.path.join(LoggingUtil.getCaseRunLogDirPath(), "TVShot_" + time.strftime("%Y%m%d%H%M%S", time.localtime()) + ".png")
                        self.feature.vp.takePicture(screenshot)
                        # 验证结果是否正确;# ocr识别;
                        ocrImgPath = os.path.join(LoggingUtil.getCaseRunLogDirPath(), "OCR_" + time.strftime("%Y%m%d%H%M%S", time.localtime()) + ".png")
                        image_util.saveCropPic(screenshot, ocrImgPath, textbox)
                        text = self.feature.OCR.getStrWithImgProcess(ocrImgPath, map_data.ocrParam[2], map_data.ocrParam[0], map_data.ocrParam[1])
                        if text is not None and text is not '':
                            if int(xls_data.params) == int(text):
                                return True, textbox, screenshot
                        
                        return False, textbox, screenshot
                    else:
                        print('ocr convert error!')
                        return False, textbox, screenshot
                #end-if
            elif xls_data.event == u"单击":
                self.feature.redRat3.sendKey(pel_data.openkey)
                # 是否成功进入关联图区;
            elif xls_data.event == u"返回":
                self.feature.redRat3.sendKey(pel_data.returnkey)
                # 是否成功返回上层图区;
            elif xls_data.event == u"弹出":
                self.feature.redRat3.sendKey(pel_data.popkey)
                # 是否成功弹出某图区;
            #end-if
        elif pel_data.type == u"按钮":
            if xls_data.event == u"单击":
                self.feature.redRat3.sendKey(pel_data.openkey)
                # 是否成功进入关联图区;
            elif xls_data.event == u"返回":
                self.feature.redRat3.sendKey(pel_data.returnkey)
                # 是否成功返回上层图区;
            elif xls_data.event == u"弹出":
                self.feature.redRat3.sendKey(pel_data.popkey)
                # 是否成功弹出某图区;
        elif pel_data.type == u"编辑框":
            pass
        
        # 默认返回True;
        return True, [], screenshot
    #end-fun

    # 根据布局方式，到达目标控件;
    def doMove2TargetCtrl(self, pel_data, map_data):
        print pel_data.id, pel_data.name, pel_data.areaid, map_data.id, map_data.name
        key, result, curbox, lastbox, screenshot = '', False, [], [], ''
        if map_data.layouttype == u"网格":
            pass
        elif map_data.layouttype == u"纵向列表":
            # 开始位置;用于循环列表;
            beginbox = []
            # 默认遍历方向;
            key = 'down'
            # 遍历计数;
            count = 0
            # 是否逆序遍历;
            isAdverse = False
            while True:
                result, curbox, screenshot = self.doVerifyOnTargetCtrl(pel_data, map_data)
                # 找到目标焦点;
                if result is True:
                    break
               
                # 正序遍历;
                if isAdverse is False:
                    # 当前焦点框与上次焦点框相同,认为到达边界端;
                    if curbox == lastbox:
                        key = 'up'#改变寻路方向;
                        # 标记已开始逆序
                        isAdverse = True
                        # 根据count计数，直接返回原本位置;
                        self.feature.redRat3.sendKey(key,count,0.2)
                    
                    # 循环列表完成一次遍历回到beginbox;
                    if curbox == beginbox:
                        break
                else:
                    # 已逆序,且再次到达另一边界端;
                    if curbox == lastbox:
                        break
                
                # 记录起始位置;
                if beginbox == []:
                    beginbox = curbox
                
                # 记录本次位置;
                lastbox = curbox
                # 下一位置;
                self.feature.redRat3.sendKey(key)
            #end-while
        elif map_data.layouttype == u"横向列表":
            # 开始位置;用于循环列表;
            beginbox = []
            # 默认遍历方向;
            key = 'right'
            # 遍历计数;
            count = 0
            # 是否逆序遍历;
            isAdverse = False
            while True:
                result, curbox, screenshot = self.doVerifyOnTargetCtrl(pel_data, map_data)
                # 找到目标焦点;
                if result is True:
                    break

                # 正序遍历;
                if isAdverse is False:
                    # 当前焦点框与上次焦点框相同,认为到达边界端;
                    if curbox == lastbox:
                        key = 'left'  # 改变寻路方向;
                        # 标记已开始逆序
                        isAdverse = True
                        # 根据count计数，直接返回原本位置;
                        self.feature.redRat3.sendKey(key, count, 0.2)

                    # 循环列表完成一次遍历回到beginbox;
                    if curbox == beginbox:
                        break
                else:
                    # 已逆序,且再次到达另一边界端;
                    if curbox == lastbox:
                        break

                # 记录起始位置;
                if beginbox == []:
                    beginbox = curbox

                # 记录本次位置;
                lastbox = curbox
                # 下一位置;
                self.feature.redRat3.sendKey(key)
            # end-while
        elif map_data.layouttype == u"标签列表":
            pass
        elif map_data.layouttype == u"图独":
            return True, [], screenshot
        # 返回结果以及坐标;
        return result, curbox, screenshot
    #end-fun

    # 结果比较;
    def doResultComparison(self, row, xls_data):
        result, desc = True, ''
        # 解析json为字典;
        try:
            data = json.loads(row.params)
        except Exception, e:
            data = {}
            desc = str(e)
        if data is None or data == {}:
            return False, u'Json参数解析出错=%s'%('数据有误' if desc == '' else desc)

        # 根据描述选择方法;
        if row.event == u"示例":
            # [{"num":13},{"num":8}]
            param1 = data[0]
            param2 = data[1]
            # 解析对应的参数;
            for r in xls_data:
                if r.num == param1['num']:
                    param1["data"] = r.eventresult
                if r.num == param2['num']:
                    param2['data'] = r.eventresult
            #end-for
            print param1,param2
        elif row.event == u"明暗对比":
            pass
        elif row.event == u"":
            pass
        elif row.event == u"":
            pass
        elif row.event == u"":
            pass
        elif row.event == u"":
            pass
        elif row.event == u"":
            pass
        elif row.event == u"":
            pass
        elif row.event == u"":
            pass
        
        # 默认返回值;
        return result, desc
    #end-fun
    
    # 保存结果;
    def doSaveResult(self, xls_data, save_path):
        # 创建工作簿;
        book = xlwt.Workbook()
        # 创建sheet;
        sheet = book.add_sheet(u'result', cell_overwrite_ok=True)
        # 创建标题行;
        row0 = [u'编号',u'类型', u'控件ID',u'描述',u'事件',u'事件参数',u'类型结果',u'事件结果']
        for i in range(len(row0)):
            sheet.write(0, i, row0[i], style = xlwt.XFStyle())
        
        index = 1
        # 写入其他数据;
        for row in xls_data:
            l = row.tolist()
            for j in range(len(l)):
                sheet.write(index, j, l[j], style = xlwt.XFStyle())
            index += 1
        #end-for

        # 保存xls;
        book.save(save_path)
    #end-fun

    def run(self):
        # 读取xls
        self.read_excel()
        # 打开数据库;
        self.get_conn()
        # 获取游标;
        self.get_cursor()
        
        # 遍历excel数据;
        for row in self.xls_data:
            if row.type == u'控件操作':
                # 获取图元信息;
                pel_data = self.getImgViewData(row.ctrlid)
                if pel_data is not None:
                    # 获取图元图区信息;
                    map_data = self.getImgAreaData(pel_data.areaid)
                    print pel_data, map_data, row
                    if map_data is not None:
                        # 执行弹出按键;
                        # 注：如果多个控件连续且都在同一个根图区时，多次执行会出问题，需完善这里的判断机制;
                        if map_data.isroot == 1:
                            if self.doVerifyOnTargetMapArea(map_data)[0] is False:
                                self.feature.redRat3.sendKey(map_data.popkey)
                                self.feature.redRat3.sendKey('down')
                        # 到达目标图元;
                        result, coodr, screenshot = self.doMove2TargetCtrl(pel_data, map_data)
                        # 结果;
                        row.typeresult = u'{"结果":%s, "坐标":%s, "图像":%s}'%('True' if result is True else 'False', str(coodr), screenshot)
                        if result is True:
                            # 执行图元事件;
                            result, coodr, screenshot = self.doCtrlEvent(row, pel_data, map_data)
                            row.eventresult = u'{"结果":%s, "坐标":%s, "图像":%s}'%('True' if result is True else 'False', str(coodr), screenshot)
                        else:
                            print("unable find target ctrl")
                            break
                    else:
                        print('map data is none')
                        break
                else:
                    print('pel data is none')
                    break
                #end-if
            elif row.type == u'结果比较':
                result, desc = self.doResultComparison(row, self.xls_data)
                row.eventresult = u'{"结果":%s, "描述":%s}'%('True' if result is True else 'False', desc)
            elif row.type == u"截屏":
                # 默认截屏一张;
                screenshot = os.path.join(LoggingUtil.getCaseRunLogDirPath(), "TVShot_" + time.strftime("%Y%m%d%H%M%S", time.localtime()) + ".png")
                self.feature.vp.takePicture(screenshot)
                row.typeresult = screenshot
        #end-for
        # 保存结果;
        self.doSaveResult(self.xls_data, r'D:\result.xls')
    #end-fun

if __name__ == "__main__":
    executor = CExecutor(r'F:\scbc-sat\command.xlsx', r'F:\scbc-sat\ui.db')
    executor.run()