import sys
import xlsxwriter
import prep
import os
import leave
import log

reload(sys)
sys.setdefaultencoding("utf-8")


def  fill_excel( worksheet ,cell, content):
    worksheet.write(cell, content)


def get_data( catagory='calc_original_sample' ):

    days = prep.get_days(8)
    z = []
    for day in days:
        if catagory == 'calc_original_sample':
            data = prep.calc_original_sample(day)
        else:
            data = prep.cal_der_sample(day)
        z.append( dict(data ))
    return z


def get_comming_data():
    days_dict  =leave.seven_day_comming_calc()
    days_range = days_dict.keys()
    days_range.sort()
    days_range.reverse()
    z = []
    for i in days_range:
        v = days_dict.get(i)
        z.append( v )
    l =[]
    for i in z:
        d = {}
        for j in i:
            k = j.get("src_name")
            v = j.get("num")
            d[k] = v
        l.append(d)
    return l


def generate_excel( excel_sheet, data, begin_row=3):
    z = data
    source_total = set()
    source =  [i.keys() for i in z]

    for i in source:
        if i:
            for j in i:
                source_total.add(j)
    column = 'A'
    row = begin_row+1
    
    source_total = list (source_total)
    source_total.sort()
    source_len = len(source_total)
    for i in source_total:
        fill_excel(excel_sheet ,column + str(row), i)
        row +=1
    row = begin_row

    #for i in z:
    #    calc_sum = "=SUM(%s%d:%s%d)"  %(column,row, column, row + source_len)
    #    fill_excel(excel_sheet, column+str(row) , calc_sum) 

    #row=begin_row+1
    column = chr(ord(column)+1)
    for i in z:
        calc_sum = "=SUM(%s%d:%s%d)"  %(column,row+1, column, row + source_len)
        fill_excel(excel_sheet, column+str(row) , calc_sum)
        row = begin_row +1
        for j in source_total:
            v = i.setdefault(j,0)
            fill_excel( excel_sheet, column+str(row) , v)
            row+=1
        column = chr(ord(column)+1)
    return row


def today_func():

    import time
    gmt = time.gmtime()
    today = map( str, [gmt.tm_year, gmt.tm_mon, gmt.tm_mday])
    today = "_".join( today )
    return today



def generate_file(filename = "test.xlsx"):

    if not filename.endswith(".xlsx"):filename +=".xlsx"
    workbook = xlsxwriter.Workbook( filename )
    worksheet = workbook.add_worksheet()
    worksheet.set_column('A:A', 20)
    source_sample="\xe5\x8e\x9f\xe5\xa7\x8b\xe6\xa0\xb7\xe6\x9c\xac"  #in unicode chinese char
    
    # in unicode chinese char
    derivate_sample='\xe5\xa4\x84\xe7\x90\x86\xe5\x8c\x85\xe6\x8b\xac\xe8\xa1\x8d\xe7\x94\x9f\xe6\xa0\xb7\xe6\x9c\xac'

    comming_sample ='\xe6\x9d\xa5\xe6\xba\x90\xe6\xa0\xb7\xe6\x9c\xac'
    worksheet.write(0,1, source_sample)
    
    days = prep.get_days(8)
    #print(days)
    count = 0
    column = 'A'
    for day in days:
        count = count +1
        column = chr(ord(column)+1)
        co = column + ":" + column
        worksheet.set_column(co , 15)
        worksheet.write(0, count, str(day))
    excel_sheet = worksheet
    original_sample_count = get_data('calc_original_sample')

    print(original_sample_count)
    last_row = generate_excel ( excel_sheet, original_sample_count, begin_row=3)
    #fill_excel( excel_sheet , 'A' + str(last_row), derivate_sample)
    last_row +=1
    fill_excel( excel_sheet,'A' + str(last_row), derivate_sample)
    derivate_sample_count = get_data('derivate')
    last_row = generate_excel(excel_sheet, derivate_sample_count, last_row)

    fill_excel( excel_sheet,'A' + str(last_row), comming_sample)
    last_row += 1
    incomming_sample_stat = get_comming_data()
    last_row = generate_excel(excel_sheet, incomming_sample_stat, last_row)

    workbook.close()

def gene_file():

    filename = "/dev/shm/" + today_func() + ".xlsx"
    if os.path.exists(filename):
        os.unlink(filename)
    generate_file( filename )
    return filename

if __name__ == "__main__":
    gene_file()