#!/usr/bin/env python
# encoding: utf-8


"""
@version: ??
@author: wtli
@license: MIT Licence
@contact: 1135577502@qq.com
@site: https://
@software: PyCharm
@file: demo.py
@time: 2019-09-27
"""
import os
import pandas as pd
import json

import requests
from openpyxl import load_workbook


def exec_xlsx(path):
    path = '/Users/liweitao/desktop/无锡机房.xlsx'
    workbook = load_workbook(path)
    idc = os.path.splitext(os.path.basename(path))[0]
    for x in workbook.sheetnames:
        name = ''
        data = []
        cabint = x
        df = pd.read_excel(path, x, index_col=None)
        df = df.fillna('')
        for y in range(workbook[x].max_row):
            try:
                row_data = df.iloc[y].to_dict()
                if '机柜' in row_data and '资产编号' in row_data and '型号' in row_data and 'raid' in row_data and '硬盘数量' in row_data and '内存' in row_data and 'cpu' in row_data and '购买时间' in row_data and '备注' in row_data and '业务用途' in row_data and '系统' in row_data and '维护人' in row_data and 'ip标签' in row_data and 'ETH0 ip' in row_data and 'eth0 mac' in row_data and 'ETH1 ip' in row_data and 'eth1 mac' in row_data and 'ETH2 ip' in row_data and 'eth2 mac' in row_data and 'ETH3 ip' in row_data and 'IDRAC地址' in row_data and '服务编号' in row_data and 'eth3 mac' in row_data:
                    try:
                        if workbook[x][y + 2][1].fill.fgColor.rgb == 'FF00B050':
                            asset_type = 1
                            name = row_data['业务用途']
                        elif workbook[x][y + 2][1].fill.fgColor.rgb == '00000000':
                            asset_type = 3
                        elif workbook[x][y + 2][1].fill.fgColor.rgb == 'FF99CCFF':
                            asset_type = 2
                        else:
                            asset_type = 4
                        if asset_type == 2:
                            for i in data:
                                if i['hostname'] == name:
                                    i['child'].append({'other_ip': json.dumps(
                                        {'ETH0 ip': row_data['ETH0 ip'], 'ETH1 ip': row_data['ETH1 ip'],
                                         'ETH2 ip': row_data['ETH2 ip'],
                                         'ETH3 ip': row_data['ETH3 ip'],
                                         'eth0 mac': row_data['eth0 mac'], 'eth1 mac': row_data['eth1 mac'],
                                         'eth2 mac': row_data['eth2 mac'],
                                         'eth3 mac': row_data['eth3 mac']}), 'ip': row_data['ip标签'],
                                        'asset_no': row_data['资产编号'],
                                        'cabint': row_data['机柜'], 'os': row_data['系统'], 'vendor': row_data['型号'],
                                        'purchase_time': row_data['购买时间'],
                                        'cpu_model': row_data['cpu'],
                                        'cpu_num': row_data['cpu数量'] if 'cpu数量' in row_data else '',
                                        'memory': row_data['内存'],
                                        'disk': row_data['硬盘数量'],
                                        'maintain': row_data['维护人'], 'idrac': row_data['IDRAC地址'],
                                        'service_no': row_data['服务编号'],
                                        'memo': row_data['备注'],
                                        'raid': row_data['raid'], 'hostname': row_data['业务用途'], 'idc': idc,
                                        'asset_type': asset_type})
                        else:
                            data.append({'other_ip': json.dumps(
                                {'ETH0 ip': row_data['ETH0 ip'], 'ETH1 ip': row_data['ETH1 ip'],
                                 'ETH2 ip': row_data['ETH2 ip'],
                                 'ETH3 ip': row_data['ETH3 ip'],
                                 'eth0 mac': row_data['eth0 mac'], 'eth1 mac': row_data['eth1 mac'],
                                 'eth2 mac': row_data['eth2 mac'],
                                 'eth3 mac': row_data['eth3 mac']}), 'ip': row_data['ip标签'],
                                'asset_no': row_data['资产编号'],
                                'cabint': row_data['机柜'], 'os': row_data['系统'], 'vendor': row_data['型号'],
                                'purchase_time': row_data['购买时间'],
                                'cpu_model': row_data['cpu'],
                                'cpu_num': row_data['cpu数量'] if 'cpu数量' in row_data else '',
                                'memory': row_data['内存'],
                                'disk': row_data['硬盘数量'],
                                'maintain': row_data['维护人'], 'idrac': row_data['IDRAC地址'],
                                'service_no': row_data['服务编号'],
                                'memo': row_data['备注'],
                                'raid': row_data['raid'], 'hostname': row_data['业务用途'], 'idc': idc,
                                'asset_type': asset_type, 'child': []})
                    except Exception as e:
                        print(e)
                        continue
            except Exception as e:
                print(e)
                continue
    return data


def dump_asset_execl(file):
    data = exec_xlsx(file)
    fail_list = []
    for x in data:
        if 'idc' in x:
            idc = Idc.objects.filter(name=x['idc']).first()
            if idc:
                pass
            else:
                idc = Idc.objects.create(name=x['idc'])
            x['idc'] = idc
        if x['asset_type'] == 1:
            if len(x['child']) > 0:
                child = x.pop('child')
            else:
                child = None

        else:
            child = None
        x['purchase_time'] = str(x['purchase_time'])
        serializer = HostSerializer(data=x)

        try:
            serializer.is_valid(raise_exception=True)
            instance = serializer.save()
            if child:
                for y in child:
                    y['parent_id'] = instance
                    serializer = HostSerializer(data=y)

        except Exception as e:
            fail_list.append({x['hostname']: str(x)})
            print(e)

    # if len(fail_list) == 0:
    #     os.remove(file)
    #     return '导入成功'
    # else:
    #     os.remove(file)
    #     return json.dumps(fail_list)


if __name__ == '__main__':
    # def monitor_pic():
    #     url = "http://192.168.10.85/zabbix" + "/index.php"
    #     session = requests.session()
    #     session.post(url, data={"name": 'wtli', 'password': 'TUxWr8EpBfNF', 'autologin': 1,
    #                             "enter": 'Sign in'})
    #     res = session.post("http://192.168.10.85/zabbix" + "/chart2.php",
    #                        data={'width': 800, 'height': 200, 'graphid': '881'})
    #     # , 'stime': '20160907090409', 'period': 3600
    #     with open('/Users/liweitao/desktop/1.jpg', 'wb') as f:
    #         f.write(res.content)
    #
    #
    # monitor_pic()

    res = requests.get('http://www.iwgame.com/favicon.ico')
    with open('/Users/liweitao/desktop/1.png','wb') as e:
        e.write(res.content)