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

# 1. 创建表结构， DataID 自增长
# 2. 写入数据
# 3. 读取数据
# 4. 上传


import pandas as pd
import numpy
import ast
import json, datetime
import platform
from models.UploadSpssMongoModel import writer_report_template


def TestPlatform():
    return platform.architecture()[0]


def mysubData(subData):
    b = []
    for i in subData:
        try:
            if numpy.isnan(i):
                i = ""
        except:
            pass
        try:
            if isinstance(i, str):
                if "'" in i:
                    i.replace("'", "")
                elif "%" in i:
                    i.replace("'", "")
                elif "\\" in i:
                    i.replace("'", "")
                if i[0: 1] == "[" and i[-1:] == "]":
                    b.append(ast.literal_eval(i))
                elif check_json_format(i):
                    b.append(json.loads(i))
                else:
                    b.append(i)
            elif isinstance(i, numpy.float64) or isinstance(i, numpy.int64) or isinstance(i, numpy.float32) or\
                    isinstance(i, numpy.int32) or isinstance(i, int) or isinstance(i, float):
                if i - int(i) == 0:
                    b.append(str(int(i)))
                else:
                    b.append(float(i))
            elif isinstance(i, bool):
                b.append(str(i))
            elif isinstance(i, object):
                b.append(str(i))
            else:
                b.append(i)
        except:
            b = []
            return b

    return b

def check_json_format(raw_msg):
    if isinstance(raw_msg, str):
        try:
            json.loads(raw_msg, encoding='utf-8')
            for key, value in json.loads(str(raw_msg).strip()).items():
                return True
        except Exception as e:
            pass
        return False
    else:
        return False


def ExcelMain(FilePath, PlatID, ProjID, tempID):
    data, dataText, dataChart, dataTable = None, None, None, None
    try:
        data = pd.read_excel(FilePath, sheet_name='数据分析')
        dataText = pd.read_excel(FilePath, sheet_name='文字描述')
        dataChart = pd.read_excel(FilePath, sheet_name='图表')
        dataTable = pd.read_excel(FilePath, sheet_name='表格')
    except Exception as e:
        pass
    # 字段
    value_labels, value_labelsT,value_labelsC,value_labelsTB, analy, content = \
        mysubData(list(data.head())), mysubData(list(dataText.head())),mysubData(list(dataChart.head())),\
        mysubData(list(dataTable.head())), [], []
    #分析方法
    for i in range(len(data)):
        row_values, analyItem = mysubData(list(data.iloc[i])), {}
        if len(row_values) > 1 and row_values[1].strip() != "":
            for valueIndex, valueItem in enumerate(value_labels):
                if valueIndex < len(row_values) and \
                    not(isinstance(row_values[valueIndex], str) and str(row_values[valueIndex]).strip() == ""):
                    analyItem[valueItem] = row_values[valueIndex]
            analy.append(analyItem)
        else:
            break
    # 文字描述
    for i in range(len(dataText)):
        row_values, textItem, dataFlag, contentFlag = mysubData(list(dataText.iloc[i])), {}, False, False
        for valueIndex, valueItem in enumerate(value_labelsT):
            if valueIndex < len(row_values) and \
                    not(isinstance(row_values[valueIndex], str) and str(row_values[valueIndex]).strip() == ""):
                dataFlag = True
                break
        if dataFlag:
            textItem["type"], contentText, contentItem = "text", {}, {}
            for valueIndex, valueItem in enumerate(value_labelsT):
                if valueIndex < len(row_values) and \
                        not(isinstance(row_values[valueIndex], str) and str(row_values[valueIndex]).strip() == ""):
                    if str(valueItem).find("(content)") >= 0:
                        contentFlag = True
                        contentItem[str(valueItem).replace("(content)", "")] = row_values[valueIndex]
                    else:
                        contentText[valueItem] = row_values[valueIndex]
            if contentFlag:
                contentText["content"] = contentItem
            textItem["content"] = contentText
            content.append(textItem)

    # 图表
    for i in range(len(dataChart)):
        row_values, chartItem = mysubData(list(dataChart.iloc[i])), {}
        if len(row_values) > 0 and row_values[0].strip() != "":
            chartItem["type"], contentChart = "wordchart", {}
            for valueIndex, valueItem in enumerate(value_labelsC):
                if valueIndex < len(row_values) and \
                        not(isinstance(row_values[valueIndex], str) and str(row_values[valueIndex]).strip() == ""):
                    contentChart[valueItem] = row_values[valueIndex]
            chartItem["content"] = contentChart
            content.append(chartItem)
        else:
            break

    # 表格
    for i in range(len(dataTable)):
        row_values, tableItem = mysubData(list(dataTable.iloc[i])), {}
        if len(row_values) > 0 and row_values[0].strip() != "":
            tableItem["type"], contentTable = "table", {}
            for valueIndex, valueItem in enumerate(value_labelsTB):
                if valueIndex < len(row_values) and \
                        not(isinstance(row_values[valueIndex], str) and str(row_values[valueIndex]).strip() == ""):
                    contentTable[valueItem] = row_values[valueIndex]
            tableItem["content"] = contentTable
            content.append(tableItem)
        else:
            break

    conditionAnaly, conditionContent, tableReplace, textReplace = [], [], [], []
    templateArray = [dict(type='title', content='', style=''), dict(type='paragraph', content='', style=''),
                     dict(type='catalog', title='', style=''), dict(type='separator', separatorType='')]
    #根据不同情况查询数据条件替换
    if tempID == "5f4773a911aaf29d30dace9b":
        conditionAnaly.append(dict(index="1", dataSourceId="5f714384004f400f8fe07f8b", algorithm="wordcloud",
                                   variable="入学年份", noCondition="true", filter={"转换分数" :{"gt" : 0}}))
        conditionContent.append(dict(type="replace", replaceFrom={"入学年份" : "2016"}, replaceTo="data1:text"))
    if len(conditionContent) > 0:
        if len(conditionAnaly) > 0:
            templateArray.append(dict(type='condition', analy=conditionAnaly, content=conditionContent))
        else:
            templateArray.append(dict(type='condition', content=conditionContent))
    if tempID == "5ee1f95cc30f48bhljrc2021":
        conditionAnaly.append(dict(index="1", dataSourceId="5ee1f95cc30f48bhljrc2021", algorithm="wordcloud",
                                   variable="school"))
        conditionAnaly.append(dict(index="2", dataSourceId="5ee1f95cc30f48bhljrc2021", algorithm="wordcloud",
                                   variable="cate"))
        conditionContent.append(dict(type="replace", replaceFrom={"school" : {"ne":"school"}}, replaceTo="data1:text"))
        conditionContent.append(dict(type="replace", replaceFrom={"cate": "1"}, replaceTo="data2:text"))
    if tempID == "5ee1fjpjs0f48stucity2020":
        conditionAnaly.append(dict(index="1", dataSourceId="5ee1fjpjs0f48student2020", algorithm="frequency",
                                   variable="city"))
        conditionContent.append(dict(type="replace", replaceFrom={"city":{"ne":"city"}}, replaceTo="data1:value"))
        conditionContent.append(dict(type="fileReplace", replace="data1:text"))
    if tempID == "5ee1fjpjs0f48stucity2021" or tempID == "5ee1fjpjs0f48zhtcity2021":
        conditionAnaly.append(dict(index="1", dataSourceId="5ee1fjpjs0f48student2021", algorithm="frequency",
                                   variable="city"))
        conditionContent.append(dict(type="replace", replaceFrom={"city":{"ne":"city"}}, replaceTo="data1:value"))
        conditionContent.append(dict(type="fileReplace", replace="data1:text"))
    if tempID == "5ee1fjpjs0f48studist2021":
        conditionAnaly.append(dict(index="1", dataSourceId="5ee1fjpjs0f48student2021", algorithm="frequency",
                                   variable="city"))
        conditionAnaly.append(dict(index="2", dataSourceId="5ee1fjpjs0f48student2021", algorithm="frequency",
                                   variable="dist"))
        conditionContent.append(dict(type="replace", replaceFrom={"city":{"ne":"city"}}, replaceTo="data1:value"))
        conditionContent.append(dict(type="replace", replaceFrom={"city": "city"}, replaceTo="data1:value"))
        conditionContent.append(dict(type="replace", replaceFrom={"dist": {"ne": "dist"}}, replaceTo="data2:value"))
        conditionContent.append(dict(type="replace", replaceFrom={"dist": "dist"}, replaceTo="data2:value"))
        conditionContent.append(dict(type="fileReplace", replace="data1:text&data2:text"))
    if len(conditionContent) > 0:
        if len(conditionAnaly) > 0:
            templateArray.append(dict(type='condition', analy=conditionAnaly, content=conditionContent))
        else:
            templateArray.append(dict(type='condition', content=conditionContent))
    if tempID == "5ed89e3dd714d125hzbg2021":
        rowDelete = ["中国音乐学院", "中国戏曲学院", "北京电影学院", "北京舞蹈学院","中央音乐学院","中央美术学院"]
        templateArray.append(dict(type='condition', rowDelete=rowDelete))
    #根据不同情况表格相关表头替换
    if tempID == "5ed89e3dd714d125jdbg2021":
        tableReplace.append(dict(cell=[0, 3], content="基本满意", position="25",
                                 condition=["北京中医药大学","北京化工大学","北京电子科技职业学院"]))
        tableReplace.append(dict(cell=[0, 3], content="基本重要", position="38", condition=["北京化工大学"]))
        tableReplace.append(dict(cell=[0, 3], content="重要", position="38", condition=["北京中医药大学"]))
        tableReplace.append(dict(cell=[0, 3], content="基本满足", position="58",
                                 condition=["北京中医药大学", "北京化工大学", "北京电子科技职业学院"]))
        tableReplace.append(dict(cell=[1, 5], content="基本满意", position="61",
                                 condition=["北京中医药大学", "北京化工大学", "北京电子科技职业学院"]))
        tableReplace.append(dict(cell=[1, 5], content="基本满意", position="63",
                                 condition=["北京中医药大学", "北京化工大学", "北京电子科技职业学院"]))
    if len(tableReplace) > 0:
        templateArray.append(dict(type='table', replace=tableReplace))
    #专科院校文字统一替换
    if tempID == "5ed89e3dd714d125hzbg2021":
        textReplace.append(dict(replace=[["本科生"], ["毕业生"]],
                                condition=["北京社会管理职业学院", "北京劳动保障职业学院", "北京财贸职业学院",
                                           "北京电子科技职业学院", "北京信息职业技术学院", "北京工业职业技术学院",
                                           "北京青年政治学院", "北京政法职业学院", "北京农业职业学院"]))
    if len(textReplace) > 0:
        templateArray.append(dict(type='text', replace=textReplace))
    templateArray.append(dict(type='analy', analy=analy, content=content))

    templateJson = dict(platId=PlatID, projId=ProjID, tempId=tempID, name='目录名称', style='模板样式', createType='',
                        autoVariable=[""], dataSource=[dict(_id='', name='', datasetId='',
                                                            filter='', joinDatasetId='', joinCondiction='')],
                        template=templateArray)


    res = writer_report_template()
    res.delete(PlatID, ProjID, tempID)
    errorCount = 0
    try:
        res.insert(templateJson)
    except Exception as e:
        errorCount += 1
    res.close()
    return errorCount


if __name__ == '__main__':
    import time

    time1 = time.time()
    PlatID = "1"
    ProjectID = "1"
    #filePathName = "E:/2021年项目/就业指导中心/模板/报告系统简单说明文档2021简单报告.xlsx"
    #tempID = "5ed89e3dd714d125jdbg2021"
    #filePathName = "E:/2021年项目/全国医学发展研究中心/CMSS2021/全国医学中心CMSS2021院校分报告.xlsx"
    #tempID = "5ee1b6cc223bbe2blcyx2021"
    #filePathName = "E:/2021年项目/全国医学发展研究中心/CMSS2021/全国医学中心CMSS2021院校分报告-教指委.xlsx"
    #tempID = "5ee1b6cc223bbe2blcyj2021"
    #filePathName = "E:/2021年项目/就业指导中心/模板/报告系统简单说明文档2021合作院校-整体本研.xlsx"
    #tempID = "5ed89e3dd714d125hzbg2021"
    #filePathName = "E:/2021年项目/就业指导中心/模板/报告系统简单说明文档2021合作院校-院系.xlsx"
    #tempID = "5ed89e3dd714d125yxbg2021"
    #filePathName = "E:/2021年项目/就业指导中心/模板/报告系统简单说明文档2021用人单位.xlsx"
    #tempID = "5ed89e3dd714d125hzdw2021"
    #filePathName = "E:/2021年项目/中小学人工智能赋能教育应用现状及课程实施调查/报告系统简单说明文档2021简单报告—教师篇.xlsx"
    #tempID = "5ee1f95cc30f48b4rgzn2021"
    #filePathName = "E:/2021年项目/中小学人工智能赋能教育应用现状及课程实施调查/报告系统简单说明文档2021简单报告—学生篇.xlsx"
    #tempID = "5ee1f95cc30f48xsrgzn2021"
    #filePathName = "E:/2021年项目/就业指导中心/模板/报告系统简单说明文档2021合作院校附表.xlsx"
    #tempID = "5ed89e3dd714d125hzfb2021"
    #filePathName = "E:/2021年项目/联泓集团/联泓集团个人绩效报告.xlsx"
    #tempID = "5ee1b6cc223bbe2blhjt2021"
    #filePathName = "E:/2021年项目/黑龙江省普通高校人才工作现状调查/20211221/黑龙江省普通高校人才工作现状调查.xlsx"
    #tempID = "5ee1f95cc30f48bhljrc2021"
    filePathName = "E:/2022年项目/福建省基础教育监测/报告系统简单说明文档2021市级报告.xlsx"
    tempID = "5ee1fjpjs0f48stucity2021"
    #filePathName = "E:/2022年项目/福建省基础教育监测/报告系统简单说明文档2021县级报告.xlsx"
    #tempID = "5ee1fjpjs0f48studist2021"
    #filePathName = "E:/2022年项目/全国医学中学/全国医学中心CMSS新生调查2021院校分报告.xlsx"
    #tempID = "5ee1b6cc223bbecmssxs2021"
    #filePathName = "E:/2022年项目/福建省基础教育监测/报告系统简单说明文档2021专题（市）报告.xlsx"
    #tempID = "5ee1fjpjs0f48zhtcity2021"
    ExcelMain(filePathName, PlatID, ProjectID, tempID)
    print(time1 - time.time())
