#!/usr/bin/python
# -*- coding: utf-8 -*-

'''
季報
'''

from copy import deepcopy

import xlrd, xlwt
import copy
import xlutils.copy

import os
import tempfile

def listdir_fullpath(d):
    return [os.path.join(d, f) for f in os.listdir(d)]


def find_dir(*paths):
    for path in paths:
        if os.path.isdir(path):
            return path
    return ''

DPublic = find_dir('P:', '/shares/Public')

TempDir =  tempfile.gettempdir()

# Fix Python 2.x.
try:
    UNICODE_EXISTS = bool(type(unicode))
except NameError:
    unicode = lambda s: str(s)

# print(DPublic)
# exit()

template = DPublic+u'/2017/手術病例統計表/台灣神經外科醫學會101年新訂手術病例統計表(20120328).xls'
template = DPublic+u'/Sync/ByYear/2017/手術病例統計表/台灣神經外科醫學會101年新訂手術病例統計表(20120328).xls'

# path = '/Public/2015/手術病例統計表/Q3'
# dest_path = '/Public/2015/手術病例統計表/2015Q3.xls'

path = DPublic+u'//2019/手術病例統計表/Q4'
dest_path = DPublic+u'//2019/手術病例統計表/2019.xls'


def copy_sheet_old(src_book, source_index, dest_book, new_name):
    '''
    workbook     == book in use
    source_index == index of sheet you want to copy (0 start)
    new_name     == name of new copied sheet
    '''

    # make a copy of desired sheet in memory
    new_sheet = copy.copy(src_book._Workbook__worksheets[source_index])

    # append copy to worksheets list
    dest_book._Workbook__worksheets.append(new_sheet)

    # find out how many sheets are in the workbook
    append_index = len(dest_book._Workbook__worksheets)-1

    # activate the last sheet in the workbook
    dest_book.set_active_sheet(append_index)

    # rename the last sheet in the workbook
    dest_book.get_sheet(append_index).set_name(new_name)


def copy_sheet(src_book, source_index, dest_book, new_name):
    dest_sheet = dest_book.add_sheet(new_name, cell_overwrite_ok=True)
    
    src_sheet = src_book.sheets()[source_index]
    for row in range(src_sheet.nrows):
        for col in range(src_sheet.ncols):
            value = src_sheet.cell(row,col).value
            dest_sheet.write(row, col, value)

    return src_sheet, dest_sheet

def copy_sheet_format(src_book, source_index, dest_book, new_name):
    '''
    src_book
    source_index == index of sheet you want to copy (0 start)
    dest_book
    new_name     == name of new copied sheet
    '''
    wb = xlutils.copy.copy(src_book)
    
    wb.save(TempDir+'/copy.xls')
    
#     exit()

    # make a copy of desired sheet in memory

#     new_sheet = copy.copy(wb._Workbook__worksheets[source_index])
    new_sheet = deepcopy(wb.get_sheet(source_index))

    # append copy to worksheets list
    dest_book._Workbook__worksheets.append(new_sheet)


#     dest_book.save('/tmp/copy.xls')
#     exit()


    # find out how many sheets are in the workbook
    append_index = len(dest_book._Workbook__worksheets)-1

    # activate the last sheet in the workbook
    dest_book.set_active_sheet(append_index)

    # rename the last sheet in the workbook
    dest_book.get_sheet(append_index).set_name(new_name)

    return src_book.sheets()[source_index], dest_book.get_sheet(append_index) 

def mk_int(cell):
#     print cell.ctype, cell.value
    if cell.ctype in [xlrd.XL_CELL_EMPTY, xlrd.XL_CELL_BLANK]:
        return 0
    if cell.ctype ==  xlrd.XL_CELL_TEXT:
        s = cell.value.strip()
        try:
            return int(s)
        except:
            return 0
    return int(cell.value)

template_book = xlrd.open_workbook(template, formatting_info=True)

# book = xlwt.Workbook(encoding='utf-8')
# book_sum = xlwt.Workbook(encoding='utf-8')

book = xlutils.copy.copy(template_book)
book._Workbook__worksheets = []
book.encodinfg='utf-8'

book_sum = xlutils.copy.copy(template_book)
book_sum._Workbook__worksheets = []
book_sum.encodinfg='utf-8'

SourceSheets = {}

for wb_file in sorted(os.listdir(path)):
    filename = wb_file.lower()
    if 'xls' in filename and 'lock' not in filename and filename[0]!='x':
        sheet_name = unicode(os.path.splitext(wb_file)[0])
        print (sheet_name)
    else:        
        continue
    
#     print os.path.join(path, wb_file)
    
    try:
        wb = xlrd.open_workbook(os.path.join(path, wb_file), formatting_info=True , on_demand=True)
    except:
        wb = xlrd.open_workbook(os.path.join(path, wb_file), formatting_info=False, on_demand=True)
        
#    wb = xlrd.open_workbook(os.path.join(path, wb_file), encoding_override="utf-8")
#    encoding = wb.encoding
#    print wb.encoding, wb.codepage
#    wb = xlutils.copy.copy(wb)
#    print wb.encoding
    src_sheet, dest_sheet = copy_sheet_format(wb, 0, book, sheet_name)
    SourceSheets[sheet_name] = src_sheet

xxx, month_sheet = copy_sheet_format(template_book, 0, book_sum, u'依月份')
xxx, center_sheet = copy_sheet(template_book, 0, book_sum, u'依中心')

for sheet in [center_sheet, month_sheet]:
    sheet.write(1, 15, '')
    sheet.write(1, 16, '')
    sheet.write(1, 17, '')
    sheet.write(1, 18, '')

skip_row = [3, 18, 21, 24, 27, 51, 55, 56, 59]



for r in range(3, 67):
    for c in range(2, 14):
        if r in skip_row:
            month_sheet.write(r, c, '')
            continue
        sum = 0
        for s in sorted(SourceSheets):
#             print r,c,s, xlwt.Utils.rowcol_to_cell(r, c)
            src_sheet = SourceSheets[s]
#             print src_sheet.cell_value(r, c)
            try:
                cell = src_sheet.cell(r, c)
            except:
                cell = None
            if cell:    
                sum += mk_int(cell)
        month_sheet.write(r, c, sum)

    if r in skip_row:
        month_sheet.write(r, 14, '')
    else:
        cell1 = xlwt.Utils.rowcol_to_cell(r, 2)
        cell2 = xlwt.Utils.rowcol_to_cell(r, 13)
        month_sheet.write(r, 14, xlwt.Formula("SUM(%s:%s)" % (cell1, cell2)))
    
col = 2
for s in sorted(SourceSheets):
    src_sheet = SourceSheets[s]
    sheet_name = s
      
#     print '\n'.join(dir(src_sheet))
      
    print( sheet_name)
    center_sheet.write(1, col, sheet_name)
#     center_sheet.write(1, col, u'132')
      
    for r in range(3, 67):
        sum = 0
        if r in skip_row:
            center_sheet.write(r, col, '')
            continue
        for c in range(2, 14):
            try:
                cell = src_sheet.cell(r, c)
            except:
                cell = None
            if cell:    
                sum += mk_int(cell)
        center_sheet.write(r, col, sum)
    col += 1
    
    for r in range(3, 67):
        if r in skip_row:
            center_sheet.write(r, col, '')
            continue
        cell1 = xlwt.Utils.rowcol_to_cell(r, 2)
        cell2 = xlwt.Utils.rowcol_to_cell(r, col-1)
        center_sheet.write(r, col, xlwt.Formula("SUM(%s:%s)" % (cell1, cell2)))

# book.save(dest_path)
book_sum._Workbook__worksheets = book_sum._Workbook__worksheets + book._Workbook__worksheets
book_sum.save(dest_path)