#!/usr/bin/env python
# -*- coding: utf-8 -*-
# @Time    : 2018/7/16 18:38
# @Author  : Orient
# @File    : create_nbs_file.py
# @Software: PyCharm

from config import *
import xlwt
import re

my_conn = MongoConnCT()
pattern = r'[:_、()（）&%％/≥＝=—-]'
adict = {
            "/": "\/",
            ":": "\:",
            "_": "\_",
            "、": "\、",
            "(": "\(",
            ")": "\)",
            "（": "\（",
            "）": "\）",
            "&": "\&",
            "%": "\%",
            "％": "\％",
            "≥": "\≥",
            "＝": "\＝",
            "=": "\=",
            "—": "\—",
            "-": "\-"

        }
class Mapping():
    def _init_(self, title, data, row,col,format_time):
        self.name = title
        self.data = data
        self.row = row
        self.col = col
        self.FormatTime = format_time

    def getTitles(self):
        datasource = '国家统计局'
        data = MongoConnCT().db['edb_mapping_table'].aggregate([
            {'$match': {'data_source': datasource}},
            {'$group': {'_id': {
                'data_source': '$data_source',
                'title': '$title',
                'modelid': '$modelid',
            }}},
            {'$project': {
                '_id': 0,
                'data_source': '$_id.data_source',
                'title': '$_id.title',
                'modelid': '$_id.modelid'
            }
            }
        ])
        dataRaw = []
        for item in data:
            dataRaw.append(item)
        # 添加type
        dataTypes = []
        n = len(dataRaw)
        pattern = re.compile(r'[0-9]{4}|[至今]{2}')
        for i in range(n):
            dataType = {}
            dataType['data_source'] = datasource
            dataType['title'] = dataRaw[i]['title']
            dataType['type'] = re.sub(pattern, 'YYYY', dataRaw[i]['title'])
            if 'modelid' in dataRaw[i].keys():
                dataType['modelid'] = dataRaw[i]['modelid']
            else:
                dataType['modelid'] = ''
            dataTypes.append(dataType)
        # title去重
        dataResults = []
        dict = {}
        for i in range(n):
            if dataTypes[i]['type'] in dict.keys():
                period = pattern.findall(dict[dataTypes[i]['type']])
                if len(period) == 0:
                    year = str(2018)
                else:
                    year = period[len(period) - 1]
                periodNew = pattern.findall(dataTypes[i]['title'])
                if len(period) == 0:
                    yearNew = str(2018)
                else:
                    yearNew = periodNew[len(periodNew) - 1]
                if yearNew > year:
                    dict[dataTypes[i]['type']] = dataTypes[i]['title']
            else:
                dict[dataTypes[i]['type']] = dataTypes[i]['title']
        title = []
        titles = []
        for item in dict.items():
            title = item[1]
            titles.append(title)
        return titles

    def getTableRaw(self,title):
        global table
        table = my_conn.db['edb_mapping_table'].find_one({"data_source": "国家统计局", "title": title},{"_id": 0, "title": 1, "data": 1})
        return table

    def getTableSize(self,title):
        global table
        row = []
        col = []
        for item in table['data']:
            row.append(item['row'])
            col.append(item['column'])
        Mapping.row = max(row)
        Mapping.col = max(col)
        return Mapping
    #获取标题配置值
    def getTitleConfigValue(self,title):
        for item in adict.items():
            title = title.replace(item[0], item[1])
        pattern = re.compile(r'[0-9]{4}|[至今]{2}')
        titleConfigValue = re.sub(pattern, '.*', title)
        return titleConfigValue
    #获取列配置值
    def getColConfigValue(self):
        global table
        colConfigValue = []
        for item in table['data']:
            if item['column'] == 0:
                colvalue = item['text']
                for symbol in adict.items():
                    colvalue = colvalue.replace(symbol[0],symbol[1])
                colConfigValue.append(colvalue)
        return colConfigValue

    def createTable(self,title,index):
        table = self.getTableRaw(title)
        workbook = xlwt.Workbook(encoding='ascii')
        style0 = xlwt.easyxf('font: name 宋体, color-index black')
        #设置模型识别页
        sheet1 = workbook.add_sheet('模型识别', cell_overwrite_ok=True)
        sheet1.write(0, 0, '模型名称', style0)
        sheet1.write(1, 0, '标题配置', style0)
        #写入模型名称
        sheet1.write(0, 1, title, style0)
        #写入标题配置
        titleRow = 1
        sheet1.write(1, 1, self.getTitleConfigValue(title))

        #写入行指标配置
        sheet1.write(titleRow + 1 , 0, '行指标配置', style0)
        sheet1.write(titleRow + 1 , 1, '', style0)

        #写入列指标配置
        sheet1.write(titleRow + 2, 0, '列指标配置', style0)
        colRow = len(self.getColConfigValue())
        for i in range(1,colRow):
            sheet1.write(titleRow + i + 2, 1, self.getColConfigValue()[i])

        #写入指标最少匹配数
        sheet1.write(colRow + titleRow + 2, 0, '指标最少匹配数', style0)
        sheet1.write(colRow + titleRow + 2, 1, 2, style0)
        sheet1col0 = sheet1.col(0)  # xlwt中是行和列都是从0开始计算的
        sheet1col0.width = 256 * 15
        sheet1col1 = sheet1.col(1)  # xlwt中是行和列都是从0开始计算的
        sheet1col1.width = 256 * 85

        # 设置指标标准化
        sheet2 = workbook.add_sheet('指标标准化', cell_overwrite_ok=True)
        sheet2.write(0, 0, '标准名称', style0)
        sheet2.write(0, 1, '@day', style0)
        sheet2col0 = sheet2.col(0)  # xlwt中是行和列都是从0开始计算的
        sheet2col0.width = 256 * 9

        #设置指标编辑页
        sheet3 = workbook.add_sheet('指标编辑', cell_overwrite_ok=True)

        for item in table['data']:
            #写入来源指标名称
            if item['column'] == 0:
                 sheet3.write(item['row'], item['column'], item['text'], style0)
            if item['column'] == 1 and item['row'] == 0:
                sheet3.write(0, 1, "目标指标ID", style0)
            if item['column'] >1:
                sheet3.write(item['row'], item['column'], item['text'], style0)
             #设置列宽
            sheet3col1 = sheet3.col(item['column'])
            sheet3col1.width = 256 * 15
        sheet3col0 = sheet3.col(0)  # xlwt中是行和列都是从0开始计算的
        sheet3col0.width = 256 * 55
        filepath = 'D:/json/NBS/配置文件/' + 'Model'+ str(index) + '.xls'
        workbook.save(filepath)
        print('Model'+ str(index))
data = Mapping()
titles = data.getTitles()
for i in range(1,len(titles)):
    title = titles[i]
    table = data.getTableRaw(title)
    data.getTitleConfigValue(title)
    data.getColConfigValue()
    data.createTable(title,i)