#!/usr/bin/env python3
# -*- coding = utf-8 -*-
# @Time :
# @Auther : 王正礼
# @File :
# @Software : Pycharm
import get_jira
import get_cf
import xlwt,xlrd,xlutils3,xlsxwriter,openpyxl
import os,re,datetime
import configparser
adress = 'dailyReport.xls'
linestart = 0

def sheet_width(sheet):
    """
    设置列宽
    :param sheet:
    :return:
    """
    width = [12,10,40,40,16,15,15,10,20,10,20,10]
    for i in range(12):
        sheet.set_column(i,i,width=width[i])  #256*width[i]

#因调用execl库问题，该函数暂未启用
def contentStyleSet(isbold=False, isdate=False, colorindex=0, horz = 0x02):
    """
    设置单元格格式
    :param isbold:是否加粗
    :param isdate:是否是数字
    :param colorindex:字体颜色选择
    :param horz:文字居中
    换行
    字号
    :return:
    """
    styleFormat = xlwt.XFStyle()
    al = xlwt.Alignment()
    al.horz = horz
    al.vert = 0x01
    # 设置自动换行
    al.wrap = 1
    styleFormat.alignment = al

    font = xlwt.Font()
    font.name = 'Times New Roman'
    font.bold = isbold
    font.colour_index = colorindex
    styleFormat.font = font

    borders = xlwt.Borders()
    borders.left = 1
    borders.bottom_colour = 0x3A
    borders.right = 1
    borders.top = 1
    borders.bottom = 1
    styleFormat.borders = borders
    return styleFormat


def formatSet(font_size=10,bold=False,font_color='#000000',align='center',valign='vcenter',border=1,text_wrap=True):
    """
    设置单元格格式
    :param font_size:字体大小
    :param bold:是否粗体
    :param font_color:字体颜色
    :param align:水平居中，left,center,rigth
    :param valign:垂直居中,top,vcenter,bottom
    :param border:边框宽度
    :param text_wrap:是否自动换行
    :return:返回单元格格式对象
    """
    format = {
    'font_size': font_size,  # 字体大小
    'bold': bold,  # 是否粗体
    'bg_color': '#FFFFFF',  # 表格背景颜色
    'fg_color': '#00FF00',
    'font_color': font_color,  # 字体颜色
    'align': align,  # 水平居中对齐
    'valign': valign,  # 垂直居中对齐
    # 'num_format': 'yyyy-mm-dd H:M:S',# 设置日期格式
    # 后面参数是线条宽度
    'border': 1,  # 边框宽度
    'top': border,  # 上边框
    'left': border,  # 左边框
    'right': border,  # 右边框
    'bottom': border,  # 底边框
    'text_wrap': text_wrap  # 是否自动换行
    }
    return  format


def purge_file(dir, pattern):
    for f in os.listdir(dir):
        if re.search(pattern, f):
            os.remove(os.path.join(dir, f))


def getdata():
    cfbook = xlrd.open_workbook('cfDaily.xls')
    jirabook = xlrd.open_workbook('jira.xls')
    jiratable = jirabook.sheet_by_index(0)
    cftable = cfbook.sheet_by_index(0)  # cdtable类型是表单对象，不是列表
    return jiratable,cftable


def idcar(jiratable):
    """
    提取路测车辆id并串联为一个字符串
    :param jiratable:jira列表对象
    :return:车辆id字符串
    """
    idarr=[]
    idstring=''
    #获取jira中车辆列表
    for i in range(jiratable.nrows-1):
        idtmp=jiratable.cell_value(i+1,2).split('|')[1]
        if i==0:
            idarr.append(idtmp)
        else:
            num = 0
            for i in idarr:
                if i != idtmp:
                    num=num+1
            if num==len(idarr):
                idarr.append(idtmp)
    #测试车辆列表拼合成一个字符串
    for i in range(len(idarr)):
        idstring=idstring+idarr[i]+' '
    return idstring


def testaddress(idstring):
    """
    获取测试场地信息
    :param idstring: 测试车辆id字符串
    :return:
    """
    testadd=''
    conf = configparser.ConfigParser()
    conf.read("./config.ini")
    beijing=conf.get("jira screen",'beijing').split('\\')
    changshu=conf.get("jira screen",'changshu').split('\\')
    for i in beijing:
        if idstring.find(i)!=-1:
            testadd=testadd+" 北京"
    for i in changshu:
        if idstring.find(i)!=-1:
            testadd=testadd+" 常熟"
    return testadd


def savedata(jiratable, cftable):
    """
    将jira、cf数据整合到日报
    :param jiratable:
    :param cftable:
    :return:
    """
    idstring=idcar(jiratable)
    testadd=testaddress(idstring)
    tody = datetime.date.today().strftime('%y%m%d')
    textlist = ['大家好',' ','以下为 ROVER5.0-配送机器人 Dev 测试进度报告:',' ','一、测试情况：','测试分支：',
                '测试场地：','测试车辆：','最大车速：','测试时间：','软件版本：','地图版本：','发版时间：',' ',
                '二、今日工作：',' ','1、Dev'+testadd+'社会道路测试','2、bug数据拷贝上传','3、看包分配jira',
                ' ','三、今日重点关注事项：']
    linecf = 33
    linejira = linecf + cftable.nrows + 2
    book = xlsxwriter.Workbook(adress)
    sheet = book.add_worksheet('Report')
    sheet_width(sheet)
    #各部分单元格格式设置
    titlestyle = book.add_format(formatSet(bold=True))
    bodystyle = book.add_format(formatSet(font_size=10,align='left'))
    otherstyle = book.add_format(formatSet(font_size=11,border=0,valign='bottom',text_wrap=False))

    # 开头问好部分文字
    sheet.merge_range(0, 0, 0, 4, '【daily 日报】QA daily test---20'+tody)
    sheet.merge_range(1, 0, 1, 2, textlist[0])
    sheet.merge_range(3, 0, 3, 4, textlist[2])
    sheet.merge_range(5, 0, 5, 1, textlist[4])

    #一、测试情况
    for i in range(5,13):
        sheet.write(i + 1, 0, textlist[i], otherstyle)
    #二、今日工作
    for i in range(14,21):
        sheet.merge_range(i + 1, 0, i + 1, 4, textlist[i])
    #三、今日关注事项
    for i in range(22,29):
        sheet.merge_range(i, 0, i, 4,'')
    #四、今日提测用例及测试结论
    sheet.merge_range(31, 0, 31, 2, '四、今日提测用例及测试结论')

    #测试分支、测试时间、发版时间
    sheet.write(6, 1, 'devel')
    sheet.write(10, 1, '20'+tody)
    sheet.merge_range(13, 1, 13, 2, '北京时间：20' + tody + ' 10:00:00')

    #一、测试情况 下拉框选项
    #测试场地
    sheet.data_validation("B8", {'validate': 'list', 'source': ['测试中心——东南站点', '测试中心——科创大厦——东南站点','测试中心——同济广场——东南站点',
                                                                '测试中心——京玉苑——东南站点', '测试中心——科创大厦——京玉苑——东南站点','测试中心——同济广场——京玉苑——东南站点']})
    sheet.merge_range(7, 1, 7, 2, '测试中心——科创大厦——东南站点')

    #路测车辆id
    sheet.write(8, 1, idstring)
    #sheet.data_validation("B9", {'validate': 'list', 'source': idstring})

    # 最大车速
    sheet.data_validation("B10", {'validate': 'list', 'source': ['2.5m/s','3m/s','3.5m/s', '4m/s','4.5m/s', '5m/s', '5.5m/s']})
    sheet.merge_range(9, 1, 9, 2, '4m/s')
    #软件版本
    sheet.data_validation("B12", {'validate': 'list', 'source': ['QA_20'+tody+'_daily','QA_20'+tody+'_daily_1',
                                                                 'QA_20'+tody+'_daily_2', 'QA_20'+tody+'_daily_3', 'QA_20'+tody+'_daily_4']})
    sheet.merge_range(11, 1, 11, 2, 'QA_20'+tody+'_daily')
    #地图版本
    sheet.data_validation("B13", {'validate': 'list', 'source': ['测试地图版本', '运营地图版本','全绑灯地图版本']})
    sheet.merge_range(12, 1, 12, 2, '测试地图版本 全绑灯地图版本')

    #daily测试用例
    for i in range(cftable.nrows):
        if i==0:
            style = titlestyle
        else:
            style = bodystyle
        sheet.write(i + linecf, 0, cftable.cell_value(i,0), style)
        sheet.write(i + linecf, 1, cftable.cell_value(i,1), style)
        sheet.write(i + linecf, 2, cftable.cell_value(i,2), style)
        sheet.write(i + linecf, 3, cftable.cell_value(i,3), style)
        sheet.write(i + linecf, 4, cftable.cell_value(i,4), style)
        sheet.write(i + linecf, 5, cftable.cell_value(i,5), style)
        sheet.write(i + linecf, 6, cftable.cell_value(i,6), style)
        sheet.write(i + linecf, 7, cftable.cell_value(i,7), style)
        sheet.write(i + linecf, 8, cftable.cell_value(i,8), style)
        sheet.write(i + linecf, 9, cftable.cell_value(i,9), style)
        sheet.write(i + linecf, 10, cftable.cell_value(i,10), style)
        sheet.write(i + linecf, 11, cftable.cell_value(i,11), style)

    #测试jira
    for i in range(jiratable.nrows):
        if i==0:
            style = titlestyle
        else:
            style = bodystyle
        sheet.write(i + linejira, 0, jiratable.cell_value(i,0), style)
        sheet.write(i + linejira, 1, jiratable.cell_value(i,1), style)
        sheet.write(i + linejira, 2, jiratable.cell_value(i,2), style)
        sheet.write(i + linejira, 3, jiratable.cell_value(i,3), style)
        sheet.write(i + linejira, 4, jiratable.cell_value(i,4), style)
        sheet.write(i + linejira, 5, jiratable.cell_value(i,5), style)
        sheet.write(i + linejira, 6, jiratable.cell_value(i,6), style)
        sheet.write(i + linejira, 7, jiratable.cell_value(i,7), style)
        sheet.write(i + linejira, 8, jiratable.cell_value(i,8), style)
        sheet.write(i + linejira, 9, jiratable.cell_value(i,9), style)
        sheet.write(i + linejira, 10, jiratable.cell_value(i,10), style)
        sheet.write(i + linejira, 11, jiratable.cell_value(i,11), style)
    book.close()


if __name__ == '__main__':
    # 删除旧文件
    purge_file('./','.xls')
    get_cf.main()
    get_jira.main()
    jiratable,cftable = getdata()[0],getdata()[1]
    savedata(jiratable,cftable)