# -*- coding: utf-8 -*-

import xlwt
import xlrd
import time
from xlutils.copy import copy
import openpyxl 
from KDissue import *
import datetime
import time
from openpyxl.styles import Font, colors, Alignment, Border, Side
from openpyxl.styles import PatternFill
from openpyxl.utils import get_column_letter   
words = ['A','B','C','D','E', 'F', 'G', 'H','I','J','K', 'L','M','N','O','P','Q','R','S','T','U']
class KDExcel():

    def __init__(self, path):
        #TODO open excel and reset some param
        self.developIssueList = []
        self.excelPath = path
        self.m_wb = openpyxl.load_workbook(self.excelPath)
        self.m_sheetNames = self.m_wb.sheetnames
        self.worksheet = self.m_wb.active
        d = datetime.datetime.now()
        self.date = str(d.year) + '-' + str(d.month) + '-' + str(d.day)

        self.excelStyle()

    def excelStyle(self):
        self.m_font = Font(size=10, name='宋体')
        self.m_alignment = Alignment(horizontal = 'left', vertical = 'top', wrapText = True, wrap_text = True)
        self.m_patternfill = PatternFill(fill_type = 'solid', fgColor = 'FFFF00')
        self.m_border = Border(left=Side(border_style='thin', color='000000'), 
                               right=Side(border_style='thin', color='000000'), 
                               top=Side(border_style='thin', color='000000'), 
                               bottom=Side(border_style='thin', color='000000'))

    def excelFill(self, _fillRow, _index = 0):
        for i in range(_index, len(words)):
            self.worksheet[words[i] + str(_fillRow)].fill = self.m_patternfill
            

    def cellstyle(self, _fillRow):
        for row in range(_fillRow):
            for i in range(len(words)):
                self.worksheet[words[i] + str(row + 6)].font = self.m_font
                self.worksheet[words[i] + str(row + 6)].alignment = self.m_alignment
                self.worksheet[words[i] + str(row + 6)].border = self.m_border


    def aotoLine(self):
        i = 0
        col_width = []
        for col in self.worksheet.columns:
            for j in range(len(col)):
                if j == 0:
                    col_width.append(len(str(col[j].value)))
                else:
                    if col_width[i] < len(str(col[j].value)):
                        col_width[i] = len(str(col[j].value))

        for i in range (len(col_width)):
            col_letter = get_column_letter(i + 1)
            if col_width[i] > 30:
                self.worksheet.column_dimensions[col_letter].width = 30
            elif col_width[i] > 10:
                self.worksheet.column_dimensions[col_letter].width = col_width[i] + 2

    def hassheet(self, model):
        if model not in self.m_sheetNames:
            #TODO add sheet
            self.m_wb.create_sheet(index = len(self.m_sheetNames), title = model)
            self.m_sheetNames.append(model)
        return True
    
    def loadCache(self, model):
        m_sheetIndex = self.m_sheetNames.index(model)
        self.worksheet = self.m_wb.worksheets[m_sheetIndex]
        _sheet_rows = self.worksheet.max_row
        _sheet_columns = self.worksheet.max_column

        _sheetList = []
        if _sheet_rows > 6:
            for i in range(6, _sheet_rows + 1):
                m_sheetrowlist = []
                for j in range(1, _sheet_columns + 1):
                    m_sheetrowlist.append(self.worksheet.cell(row = i, column = j).value)
                del m_sheetrowlist[5:8] 
                del m_sheetrowlist[8:9]
                if m_sheetrowlist[0] == None:
                    if "reopen" in str(m_sheetrowlist[4]):
                        _l = m_sheetrowlist[4:]
                        del(_l[1])
                        _tmprp = JiraReopen(_l)
                        _sheetList[len(_sheetList) - 1].appendReopen(_tmprp)
                        continue
                # print(m_sheetrowlist)
                _isse = JiraIssue()
                _isse.insertParam(m_sheetrowlist)
                _sheetList.append(_isse)
        
        self.removemergecells()
        self.removemergecells()

        return _sheetList
    
    def getoldsheetlist(self, model):
        m_sheetIndex = self.m_sheetNames.index(model)
        self.worksheet = self.m_wb.worksheets[m_sheetIndex]
        _sheet_rows = self.worksheet.max_row
        _sheet_columns = self.worksheet.max_column

        _sheetList = []
        if _sheet_rows > 6:
            for i in range(6, _sheet_rows):
                m_sheetrowlist = []
                for j in range(1, _sheet_columns + 1):
                    m_sheetrowlist.append(self.worksheet.cell(row = i, column = j).value)
                del m_sheetrowlist[5:8] 
                del m_sheetrowlist[8:9]
                if m_sheetrowlist[0] == None:
                    continue
                # print(len(m_sheetrowlist))
                # print(m_sheetrowlist)
                _isse = JiraIssue()
                _isse.insertParam([m_sheetrowlist[0], m_sheetrowlist[1], m_sheetrowlist[2], m_sheetrowlist[3], 
                                   m_sheetrowlist[4], m_sheetrowlist[5], m_sheetrowlist[6], m_sheetrowlist[7], 
                                   m_sheetrowlist[8], m_sheetrowlist[9], m_sheetrowlist[10], m_sheetrowlist[12], 
                                   '', '', '', '', ''])
                _sheetList.append(_isse)
        return _sheetList

    def removemergecells(self):
        _c_ranges = self.worksheet.merged_cell_ranges
        for cellrange in _c_ranges:
            for r in range(6, self.worksheet.max_row + 1):
                if (str("E" + str(r)) in cellrange) or (str("K" + str(r)) in cellrange):
                    # print("get the merge row ..", r)
                    self.worksheet.unmerge_cells(start_row = r, start_column = 5, end_row = r, end_column = 8)
                    self.worksheet.unmerge_cells(start_row = r, start_column = 11, end_row = r, end_column = 12)
                    _c_ranges.remove(cellrange)
        
        for row in range(6, self.worksheet.max_row + 1):
            self.worksheet.delete_rows(6)
        

    def dealData(self, _list, model = ""):
        if model == 'TeachUI':
            self.insertData(_list, model)
        elif model == 'ManageUnit':
            self.insertData(_list, model)
        elif model == 'BoardCtrl':
            self.insertData(_list, model)
        elif model == 'Kinematics':
            self.insertData(_list, model)
        elif model == 'Servo':
            self.insertData(_list, model)
        elif model == 'Electron':
            self.insertData(_list, model)
        elif model == 'Machine':
            self.insertData(_list, model)
        elif model == 'Develop':
            self.insertData(_list, model, True)

    def insertData(self, _list, model = "", dealdevelop = False):
        if self.hassheet(model):
            exls_list = self.loadCache(model)
            for jira_issue in _list:
                if not dealdevelop:
                    if str(jira_issue._type) == u'开发内建问题':
                        self.developIssueList.append(jira_issue)
                        continue
                _has_issue = False
                for excel_issue in exls_list:
                    # print("excel_issue._key {0}   jira_issue._key  {1}".format(excel_issue._key, jira_issue._key))
                    if excel_issue._key == str(jira_issue._key):
                        if jira_issue._handler == u'姚步堂':
                            jira_issue._handler = excel_issue._handler
                        # print("has catched same issue ~~")
                        _index = exls_list.index(excel_issue)
                        #TODO anly
                        _has_issue = True
                        _reopencount = excel_issue.reopencount()
                        if _reopencount >= 1:
                            _reopen = excel_issue.reopenList[_reopencount -1]
                            if str(_reopen._status) == u'已解决' or str(_reopen._status) == u'已关闭':
                                if str(jira_issue._status) != u'已解决' and str(jira_issue._status) != u'已关闭':
                                    #TODO insert a row at the issue bottom
                                    print("excel_issue has reopen case")
                                    _reopen_id = "reopen_" + str(_reopencount)
                                    _tmpreopen = JiraReopen([_reopen_id, jira_issue._rootcause, 
                                                             None, jira_issue._update_time, None, 
                                                             None, None, jira_issue._handler, 
                                                             jira_issue._updater, None, None, jira_issue._status])
                                    _tmpreopen.needdraw = True
                                    excel_issue.appendReopen(_tmpreopen)
                                    print("---------------------------------------------------case 1")
                                    excel_issue.printparam()
                                else:
                                    _reopen = excel_issue.reopenList[_reopencount -1]
                                    _reopen.update([_reopen._id, jira_issue._rootcause, 
                                                             jira_issue._fix_discrip, _reopen._update_time, jira_issue._fixVersion, 
                                                             jira_issue._howtocheck, jira_issue._checkdiscrip, jira_issue._handler, 
                                                             jira_issue._updater, jira_issue._result, jira_issue._check_time, jira_issue._status])
                                    # _reopen.needdraw = True
                                    excel_issue.reopenList[_reopencount -1] = _reopen
                                    print("---------------------------------------------------case 2")
                                    excel_issue.printparam()
                            else:
                                _reopen = excel_issue.reopenList[_reopencount -1]
                                if str(jira_issue._status) == u'已解决' or str(jira_issue._status) == u'已关闭':
                                    _reopen.needdraw = True
                                    jira_issue._update_time = _reopen._update_time
                                _reopen.update([_reopen._id, jira_issue._rootcause, 
                                                jira_issue._fix_discrip, jira_issue._update_time, jira_issue._fixVersion, 
                                                jira_issue._howtocheck, jira_issue._checkdiscrip, jira_issue._handler, 
                                                jira_issue._updater, jira_issue._result, jira_issue._check_time, jira_issue._status])
                                excel_issue.reopenList[_reopencount -1] = _reopen
                                print("---------------------------------------------------case 3")
                                excel_issue.printparam()

                            # if str(jira_issue._status) == u'已解决' or str(jira_issue._status) == u'已关闭':
                            #     if _reopen._status != u'已解决' and _reopen._status != u'已关闭':
                            #         _reopen = excel_issue.reopenList[_reopencount -1]
                            #         _reopen.update([_reopen._id, jira_issue._rootcause, 
                            #                                  jira_issue._fix_discrip, jira_issue._update_time, jira_issue._fixVersion, 
                            #                                  jira_issue._howtocheck, jira_issue._checkdiscrip, jira_issue._handler, 
                            #                                  jira_issue._updater, jira_issue._result, jira_issue._check_time, jira_issue._status])
                            #         _reopen.needdraw = True
                            #         excel_issue.reopenList[_reopencount -1] = _reopen
                            # if str(jira_issue._status) != u'已解决' and str(jira_issue._status) != u'已关闭':
                            #     if _reopen._status != u'已解决' and _reopen._status != u'已关闭':
                            #         _reopen = excel_issue.reopenList[_reopencount -1]
                            #         _reopen.update([_reopen._id, jira_issue._rootcause, 
                            #                                  jira_issue._fix_discrip if jira_issue._fix_discrip != _reopen._fix_discrip else None,
                            #                                  jira_issue._update_time, None, 
                            #                                  jira_issue._howtocheck if jira_issue._howtocheck != _reopen._howtocheck else None,
                            #                                  jira_issue._checkdiscrip if jira_issue._checkdiscrip != _reopen._checkdiscrip else None, 
                            #                                  jira_issue._handler, None, None, None, jira_issue._status])
                            #         _reopen.needdraw = True
                            #         excel_issue.reopenList[_reopencount -1] = _reopen

                            exls_list[_index] = excel_issue
                            # continue
                        else:
                            #TODO has no reopen case
                            if (excel_issue._status == u'已解决' or excel_issue._status == u'已关闭'):
                                if str(jira_issue._status) != "已解决" and str(jira_issue._status) != "已关闭":
                                    #TODO insert a row at the issue bottom
                                    print("excel_issue has no reopen case")
                                    _reopen_id = "reopen_0"
                                    _tmpreopen = JiraReopen([_reopen_id, jira_issue._rootcause, 
                                                             None, jira_issue._update_time, None, 
                                                             None, None, jira_issue._handler, 
                                                             jira_issue._updater, None, None, jira_issue._status])
                                    _tmpreopen.needdraw = True
                                    excel_issue.appendReopen(_tmpreopen)
                                    exls_list[_index] = excel_issue
                                    print("---------------------------------------------------case 4")
                                    excel_issue.printparam()
                                else:
                                    jira_issue._update_time = excel_issue._update_time
                                    exls_list[_index] = jira_issue
                                # continue
                            else:
                                if str(jira_issue._status) == u'已解决' or str(jira_issue._status) == u'已关闭':
                                    jira_issue.needdraw = True
                                    jira_issue._update_time = excel_issue._update_time
                                exls_list[_index] = jira_issue
                        continue
                            # if str(jira_issue._status) == u'已解决' or str(jira_issue._status) == u'已关闭':
                            #     if excel_issue._status != u'已解决' and excel_issue._status != u'已关闭':
                            #         _reopen = excel_issue.reopenList[_reopencount -1]
                            #         _reopen.update([_reopen._id, jira_issue._rootcause, 
                            #                                  jira_issue._fix_discrip, jira_issue._update_time, jira_issue._fixVersion, 
                            #                                  jira_issue._howtocheck, jira_issue._handler, jira_issue._checkdiscrip, 
                            #                                  jira_issue._check_time, jira_issue._status, jira_issue._updater, jira_issue._result])
                
                if not _has_issue:
                    #TODO insert row at the bottom
                    jira_issue.needdraw = True
                    exls_list.insert(0, jira_issue)
            
            self.WriteExcel(exls_list)
    
    def WriteExcel(self, _list):
        m_timeValue = self.worksheet.cell(row = 2, column = 17).value
        self.worksheet['Q1'] = '上次修订时间：' + m_timeValue[7:]
        self.worksheet['Q2'] = '本次修订时间：' + str(self.date)
        
        self.excelStyle()
        # self.execelWidth()
        _row = 0
        # print("WriteExcel current index _list len = ", len(_list))
        # _list.reverse()

        for missue in _list:
            if missue._key == '' or missue._key == None:
                continue

            self.worksheet.row_dimensions[_row + 6].height = 142.5

            self.worksheet.cell(row = _row + 6, column = 1,  value  = str(missue._key))
            self.worksheet.cell(row = _row + 6, column = 2,  value  = str(missue._type))
            self.worksheet.cell(row = _row + 6, column = 3,  value  = str(missue._create_time))
            self.worksheet.cell(row = _row + 6, column = 4,  value  = str(missue._version))
            self.worksheet.cell(row = _row + 6, column = 5,  value  = str(missue._discrip))
            self.worksheet.cell(row = _row + 6, column = 9,  value  = str(missue._ctreater))
            self.worksheet.cell(row = _row + 6, column = 10, value  = str(missue._rootcause))
            self.worksheet.cell(row = _row + 6, column = 11, value  = str(missue._fix_discrip))
            self.worksheet.cell(row = _row + 6, column = 13, value  = str(missue._update_time))
            self.worksheet.cell(row = _row + 6, column = 14, value = str(missue._fixVersion))
            self.worksheet.cell(row = _row + 6, column = 15, value = str(missue._howtocheck))
            self.worksheet.cell(row = _row + 6, column = 16, value = str(missue._checkdiscrip))
            self.worksheet.cell(row = _row + 6, column = 17, value = str(missue._handler))
            self.worksheet.cell(row = _row + 6, column = 18, value = str(missue._updater))
            self.worksheet.cell(row = _row + 6, column = 19, value = str(missue._result))
            self.worksheet.cell(row = _row + 6, column = 20, value = str(missue._check_time))
            self.worksheet.cell(row = _row + 6, column = 21, value = str(missue._status))

            if missue.needdraw:
                self.excelFill(_row + 6)

            self.worksheet.merge_cells(start_row = _row + 6, start_column = 5, end_row = _row + 6, end_column = 8)
            self.worksheet.merge_cells(start_row = _row + 6, start_column = 11, end_row = _row + 6, end_column = 12)

            for _reopen in missue.reopenList:
                print("missue.reopenList len {0}  key = {1}  Row = {2}".format(len(missue.reopenList), missue._key, _row))
                self.worksheet.insert_rows(_row + 6 + 1)

                self.worksheet.cell(row = _row + 7, column = 5,  value  = str(_reopen._id))
                self.worksheet.cell(row = _row + 7, column = 10, value  = str(_reopen._rootcause))
                self.worksheet.cell(row = _row + 7, column = 11, value  = str(_reopen._fix_discrip))
                self.worksheet.cell(row = _row + 7, column = 13, value  = str(_reopen._update_time))
                self.worksheet.cell(row = _row + 7, column = 14, value = str(_reopen._fixVersion))
                self.worksheet.cell(row = _row + 7, column = 15, value = str(_reopen._howtocheck))
                self.worksheet.cell(row = _row + 7, column = 16, value = str(_reopen._checkdiscrip))
                self.worksheet.cell(row = _row + 7, column = 17, value = str(_reopen._handler))
                self.worksheet.cell(row = _row + 7, column = 18, value = str(_reopen._updater))
                self.worksheet.cell(row = _row + 7, column = 19, value = str(_reopen._result))
                self.worksheet.cell(row = _row + 7, column = 20, value = str(_reopen._check_time))
                self.worksheet.cell(row = _row + 7, column = 21, value = str(_reopen._status))

                self.worksheet.merge_cells(start_row = _row + 7, start_column = 5, end_row = _row + 7, end_column = 8)
                self.worksheet.merge_cells(start_row = _row + 7, start_column = 11, end_row = _row + 7, end_column = 12)

                if _reopen.needdraw:
                    self.excelFill(_row + 7, 5)

                _row += 1
            _row += 1
        
        self.cellstyle(_row)

    def close(self):
        self.m_wb.save(self.excelPath)