#!/usr/bin/env python3
#-*- coding:utf-8 -*-


try:
    import sys
    import os
    import time
    from gspylib.inspection.common import SharedFuncs

    sys.path.append(sys.path[0] + '/inspection/lib')
    from gspylib.inspection.lib.xlsxwriter import Workbook
except ImportError as ie:
    sys.exit("[GAUSS-52200] : Unable to import module: %s." % str(ie))

RESOURCEPATH = os.path.realpath(os.path.join(os.path.split(os.path.realpath(__file__))[0], os.path.pardir, "lib"))


class ExcelWriter(object):
    def __init__(self, srcfile, imgfile=None, logofile=None):

        # create a excel file
        self.workbook = Workbook(srcfile)
        self.sitename = ""
        self.curentraw = 1
        self.TitleStyle = self.workbook.add_format({
            'font_size': 24,
            'bold': True,
            'bg_color': '#92d050',
            'font_color': '#FEFEFE',
            'align': 'center',
            'top': 2,
            'left': 2,
            'right': 2,
            'bottom': 2,
            'border': 1
        })
        self.ItemStyle = self.workbook.add_format({
            'font_size': 16,
            'bold': True,
            'bg_color': '#008080',
            'font_color': '#FEFEFE',
            'align': 'center',
            'top': 2,
            'left': 2,
            'right': 2,
            'bottom': 2,
            'border': 1
        })
        self.ValueStyle = self.workbook.add_format({
            'font_size': 12,
            'font_color': '#000000',
            'align': 'left',
            'top': 2,
            'left': 2,
            'right': 2,
            'bottom': 2,
            'border': 1
        })

        # get image and logo file
        if (imgfile is None):
            imgfile = os.path.join(RESOURCEPATH, "profile.png")
        if (logofile is None):
            logofile = os.path.join(RESOURCEPATH, "logo.png")
        self.writeprofile(imgfile, logofile)
        # create sum worksheet object
        # create detail worksheet object
        self.checkdetailworksheet = self.workbook.add_worksheet('检查结果')
        # set width of A column to 80
        self.checkdetailworksheet.set_column('A:J', 25)
        self.checkdetailworksheet.hide_gridlines(option=2)
        title = ['检查项名称', '分类', '节点名称', '检查标准', '检查结果', '结果信息', '原始信息', '分析结果', '修复建议']
        self.checkdetailworksheet.write_row(0, 0, title, self.ItemStyle)

    def formatecell(self, border, color, align, bold, fontsize):
        # define format_title
        aformat = self.workbook.add_format()
        # define format_title with border 1pix
        aformat.set_border(border)
        # define format_title with background color '#cccccc'
        aformat.set_bg_color(color)
        # define format_title
        aformat.set_align(align)
        # define format_title
        aformat.set_bold(bold)
        aformat.set_font_size(fontsize)
        return aformat

    def writeprofile(self, picfile, logofile):
        # create a front page
        worksheet = self.workbook.add_worksheet('封面')
        worksheet.hide_gridlines(option=2)
        # insert title image in C10
        worksheet.insert_image('A1', picfile)
        aformat = self.formatecell(1, '#cccccc', 'center', 1, 12)
        #add information
        worksheet.merge_range('A24:B24', '巡检时间', aformat)
        aformat = self.formatecell(1, '#ffffff', 'center', 0, 12)
        lstCurDateTime = time.localtime(time.time())
        strCurDateTime = "%04d-%02d-%02d %02d:%02d" % (
            lstCurDateTime[0], lstCurDateTime[1], lstCurDateTime[2], lstCurDateTime[3], lstCurDateTime[4])
        worksheet.merge_range('C24:H24', strCurDateTime, aformat)
        aformat = self.formatecell(1, '#cccccc', 'center', 1, 12)
        worksheet.merge_range('A25:B25', '工具版本号', aformat)
        aformat = self.formatecell(1, '#ffffff', 'center', 0, 12)
        worksheet.merge_range('C25:H25', SharedFuncs.getVersion(), aformat)
        # insert logo image in D34
        worksheet.insert_image('D34', logofile)
        aformat = self.formatecell(0, '#ffffff', 'center', 0, 12)
        worksheet.merge_range('A38:H38', '版权所有©华为技术有限公司 2008-2016。 保留一切权利。', aformat)

    def writecheckdetail(self, name, category, host, std, rst, rstd, raw, analysis, suggestion):

        data = []
        data.append(name)
        data.append(category)
        data.append(host)
        data.append(std)
        data.append(rst)
        data.append(rstd)
        data.append(raw)
        data.append(analysis)
        if 'NG' == rst:
            data.append(suggestion)
        self.checkdetailworksheet.write_row(self.curentraw, 0, data, self.ValueStyle)
        self.curentraw += 1

    def writesum(self):
        self.sumworksheet.set_column('A:H', 25)
        self.sumworksheet.hide_gridlines(option=2)

        self.sumworksheet.merge_range('A1:F2', '巡检报告', self.TitleStyle)
        self.sumworksheet.merge_range('A7:F7', '统计结果汇总图', self.ItemStyle)
        self.sumworksheet.merge_range('A27:F27', '统计结果统计数据', self.ItemStyle)

        title = ['环境按节点统计', '节点总数', '合格节点数', '不合格节点数', '节点合格率', '节点不合格率']
        self.sumworksheet.write_row(27, 0, title, self.ItemStyle)

        data = [self.sitename, '=COUNTA(A35:A5000)', '=COUNTIF(D35:D5000,0)', '=B29-C29',
                '=IF(AND(C29/B29<1,C29/B29>0.99),0.99,IF(AND(C29/B29>0,C29/B29<0.01),0.01,C29/B29))', '=1-E29']
        self.sumworksheet.write_row(28, 0, data, self.ValueStyle)

        graphType = {'type': 'column'}
        loc = 'A8'
        series = [{'name': '=汇总信息!$C$28', 'values': '=汇总信息!$C$29', },
                  {'name': '=汇总信息!$D$28', 'values': '=汇总信息!$D$29', }]
        size = {'width': 350, 'height': 350, }
        column_title = {'name': '节点合格率', }
        chart = self.drawchart(graphType, series, {}, {}, size, column_title, 37)
        self.sumworksheet.insert_chart(loc, chart)

        title = ['环境按巡检项统计', '巡检总项数', '合格项数', '不合格项数', '巡检项合格率', '巡检项不合格率']
        self.sumworksheet.write_row(29, 0, title, self.ItemStyle)

        data = [self.sitename, '=COUNTA(E35:E5000)', '=COUNTIF(H35:H5000,0)', '=B31-C31',
                '=IF(AND(C31/B31<1,C31/B31>0.99),0.99,IF(AND(C31/B31>0,C31/B31<0.01),0.01,C31/B31))', '=1-E31']
        self.sumworksheet.write_row(30, 0, data, self.ValueStyle)

        graphType = {'type': 'column'}
        loc = 'D8'
        series = [{'name': '=汇总信息!$C$30', 'values': '=汇总信息!$C$31', },
                  {'name': '=汇总信息!$D$30', 'values': '=汇总信息!$D$31', }]
        size = {'width': 350, 'height': 350, }
        column_title = {'name': '巡检项合格率', }
        chart = self.drawchart(graphType, series, {}, {}, size, column_title, 38)
        self.sumworksheet.insert_chart(loc, chart)

        self.sumworksheet.merge_range('A33:D33', '节点巡检结果统计', self.ItemStyle)

        title = ['节点名', '巡检总数', '合格项数', '不合格项数']
        self.sumworksheet.write_row(33, 0, title, self.ItemStyle)

        self.sumworksheet.merge_range('E33:H33', '巡检项结果统计', self.ItemStyle)
        title = ['巡检项', '巡检总数', '合格项数', '不合格项数']
        self.sumworksheet.write_row(33, 4, title, self.ItemStyle)

        self.write_check_result(self.host_OKcount, self.host_NGcount, True)
        self.write_check_result(self.checkitem_OKcount, self.checkitem_NGcount)

    def drawchart(self, graph, series, x_axis, y_axis, size, title, style):
        chart = self.workbook.add_chart(graph)
        for i in range(len(series)):
            chart.add_series(series[i])
        chart.set_x_axis(x_axis)
        chart.set_y_axis(y_axis)
        chart.set_size(size)
        chart.set_title(title)
        chart.set_style(style)
        return chart

    def close(self):
        # close the document
        self.workbook.close()

    def write_check_result(self, ok_dict, ng_dict, is_host=False):
        sunmrow = 34
        for item in list(set(ok_dict.keys()) | set(ng_dict.keys())):
            if ok_dict.__contains__(item):
                OKcount = ok_dict[item]
            else:
                OKcount = 0
            if ng_dict.__contains__(item):
                NGcount = ng_dict[item]
            else:
                NGcount = 0
            data = list()
            data.append(item)
            if is_host:
                total = '=C%s+D%s' % (sunmrow + 1, sunmrow + 1)
            else:
                total = '=G%s+H%s' % (sunmrow + 1, sunmrow + 1)
            data.append(total)
            data.append(OKcount)
            data.append(NGcount)
            if is_host:
                self.sumworksheet.write_row(sunmrow, 0, data, self.ValueStyle)
            else:
                self.sumworksheet.write_row(sunmrow, 4, data, self.ValueStyle)
            sunmrow += 1
