# -*- coding: utf-8 -*-
"""
Created on Wed Jan 12 15:43:33 2022

@author: 田中玉
"""

import xlsxwriter
import numpy as np
import pandas as pd
import os
import zipfile
import shutil
import time


from docx.shared import Pt
from docx.enum.style import WD_STYLE_TYPE
from docx.oxml.ns import qn
from docx.shared import Inches
from docx import Document
import xlsxwriter
import numpy as np
import pandas as pd
import os
import matplotlib.pyplot as plt
from openpyxl.styles import Alignment,Font
from openpyxl import Workbook, load_workbook
from openpyxl.drawing.image import Image
import openpyxl
import xlrd,xlsxwriter
from docx.shared import Pt
from math import ceil
import docx
from datetime import datetime
import datetime, time
from datetime import timedelta




path = 'D:/_5G周报'
path_5g='D:/_5G周报/原始数据'+'//'
path_5g_busy='D:/_5G周报/忙时数据'+'//'
path_out='D:/_5G周报/输出文件'+'//'

os.chdir(path)

files_5G = os.listdir(path_5g)
zte_5G_files=[x for x in files_5G if '5G' in x]
eric_5G_files = [x for x in files_5G if '爱立信' in x]

files_5G_busy = os.listdir(path_5g_busy) 
zte_5G_busy_files=[x for x in files_5G_busy if '5G' in x]
eric_5G_busy_files = [x for x in files_5G_busy if '爱立信' in x]




df_enodeb_name  = pd.read_excel('物理站址清单.xlsx',encoding = 'utf-8')
#处理爱立信基础数据
df_eric_5G_traffic = pd.DataFrame()
for file in eric_5G_files:    
    df_tmp = pd.read_excel(path_5g+file)
    df_tmp.fillna(0,inplace=True)
    df_tmp.columns
    df_tmp['地市']=df_tmp['ManagedElement'].map(lambda x : list(x.split('_')[1])[0])+df_tmp['ManagedElement'].map(lambda x : list(x.split('_')[1])[1])
    df_tmp=df_tmp[['地市','DATE_ID','cell','PLMN','RLC_RxBytesUl','RLC_TxBytesDl','RRC_ConnMax']]
    df_tmp=df_tmp[df_tmp['地市'].isin(['QJ'])]
    df_tmp['地市']=df_tmp['地市'].map(lambda x :x.replace('QJ','曲靖'))
    df_tmp['上行RLC层用户面总流量(GB)']=df_tmp['RLC_RxBytesUl']/1024
    df_tmp['下行RLC层用户面总流量(GB)']=df_tmp['RLC_TxBytesDl']/1024
    df_tmp['总流量']=(df_tmp['RLC_RxBytesUl']+df_tmp['RLC_TxBytesDl'])/1024
    df_tmp['厂家']='爱立信'
    df_tmp['日期']=df_tmp['DATE_ID'].map(lambda x : str(x).split()[0].replace('-','/'))
    df_tmp['基站ID+小区ID']=df_tmp['cell'].map(lambda x :x.split('_')[0])+"_"+df_tmp['cell'].map(lambda x :x.split('_')[1])
    df_tmp['标记']=df_tmp['日期']+'_'+df_tmp['基站ID+小区ID']
    df_eric_5G_traffic=df_eric_5G_traffic.append(df_tmp)
    
#处理爱立信忙时数据
df_eric_5G_traffic_bush = pd.DataFrame()
for file in eric_5G_busy_files:    
    df_tmp = pd.read_excel(path_5g_busy+file)
    df_tmp.fillna(0,inplace=True)
    df_tmp.columns
    df_tmp=df_tmp[['date_id','gNB_cell','PRB_UsedDl','PRB_AvailDl','UL_User_Avg_ThroughPut_Rate_Mbps','DL_User_Avg_ThroughPut_Rate_Mbps']]
    df_tmp['日期']=df_tmp['date_id'].map(lambda x : str(x).split()[0].replace('-','/'))
    df_tmp['PRB利用率']=df_tmp['PRB_UsedDl']/df_tmp['PRB_AvailDl']
    df_tmp['基站ID+小区ID']=df_tmp['gNB_cell'].map(lambda x :x.split('_')[0])+"_"+df_tmp['gNB_cell'].map(lambda x :x.split('_')[1])
    df_tmp['标记']=df_tmp['日期']+'_'+df_tmp['基站ID+小区ID']
    df_tmp=df_tmp[['标记','UL_User_Avg_ThroughPut_Rate_Mbps','DL_User_Avg_ThroughPut_Rate_Mbps','PRB利用率']]
    df_eric_5G_traffic_bush=df_eric_5G_traffic_bush.append(df_tmp)
    
df_eric_5G=pd.merge(df_eric_5G_traffic,df_eric_5G_traffic_bush,how='left',on='标记')
df_eric_5G.columns
df_eric_5G.rename(columns={'UL_User_Avg_ThroughPut_Rate_Mbps':'上行体验速率',
                           'DL_User_Avg_ThroughPut_Rate_Mbps':'下行体验速率',
                           'RRC_ConnMax':'RRC最大连接用户数',},inplace =True)
df_eric_5G1=df_eric_5G[['地市','厂家','基站ID+小区ID','日期','PLMN','RRC最大连接用户数','总流量','上行体验速率','下行体验速率','PRB利用率']]


with pd.ExcelWriter(path_out+'给联通数据.xlsx') as f:
    df_eric_5G1.to_excel(f, index =False)
df_eric_5G2=pd.merge(df_eric_5G,df_enodeb_name,how='left',on='基站ID+小区ID')
df_eric_5G2=df_eric_5G2[['地市','厂家','基站ID+小区ID','区县','基站名称','日期','PLMN','RRC最大连接用户数','上行RLC层用户面总流量(GB)','下行RLC层用户面总流量(GB)','总流量','PRB利用率']]

#
df_zte_5G_traffic = pd.DataFrame()
for x in zte_5G_files:
    file_zip = zipfile.ZipFile(path_5g+x, 'r')
    file_zip.extractall(path_5g)
    file_zip.close()
    zte_list=os.listdir(path_5g)
    zte_list=[x for x in zte_list if '5G' in x and 'xlsx'in x]
    df_tmp = pd.read_excel(path_5g+zte_list[0])
    df_tmp['地市']=df_tmp['子网名称'].map(lambda x:x[:2])    
    df_tmp['厂家']='中兴'
    df_tmp['基站ID+小区ID']=df_tmp['gNBId'].astype(str)+"_"+df_tmp['cellId'].astype(str)
    df_tmp['日期']=df_tmp['开始时间'].map(lambda x : str(x).split()[0].replace('-','/'))
    df_tmp['PLMN']=df_tmp['gNBplmn'].map(lambda x:int(x.replace('-','')))
    df_tmp['RRC最大连接用户数']=df_tmp['RRC连接最大连接用户数']
    df_tmp['上行RLC层用户面总流量(GB)']=df_tmp['上行RLC层用户面流量_1609386307605(GB)']
    df_tmp['下行RLC层用户面总流量(GB)']=df_tmp['下行RLC层用户面流量_1609386307658(GB)']
    df_tmp['总流量']=df_tmp['RLC层用户面总流量（GB)']
    df_tmp['标记']=df_tmp['日期']+'_'+df_tmp['基站ID+小区ID']
    df_tmp=df_tmp[['地市','厂家','基站ID+小区ID','日期','PLMN','RRC最大连接用户数','上行RLC层用户面总流量(GB)','下行RLC层用户面总流量(GB)','总流量','标记']]
    df_zte_5G_traffic=df_zte_5G_traffic.append(df_tmp)
    os.remove(path_5g+zte_list[0])

df_zte_5G_traffic_busy= pd.DataFrame()
for x in zte_5G_busy_files:
    file_zip = zipfile.ZipFile(path_5g_busy+x, 'r')
    file_zip.extractall(path_5g_busy)
    file_zip.close()
    zte_list=os.listdir(path_5g_busy)
    zte_list=[x for x in zte_list if '5G' in x and 'xlsx'in x]
    df_tmp = pd.read_excel(path_5g_busy+zte_list[0])
    df_tmp.columns
    df_tmp['基站ID+小区ID']=df_tmp['gNBId'].astype(str)+"_"+df_tmp['cellId'].astype(str)
    df_tmp['日期']=df_tmp['开始时间'].map(lambda x : str(x).split()[0].replace('-','/'))
    df_tmp['PRB利用率']=df_tmp['下行PRB利用率-tucq']
    df_tmp['标记']=df_tmp['日期']+'_'+df_tmp['基站ID+小区ID']
    df_tmp=df_tmp[['标记','PRB利用率']]
    df_tmp=df_tmp.drop_duplicates()#整表去重
    df_zte_5G_traffic_busy=df_zte_5G_traffic_busy.append(df_tmp)
    os.remove(path_5g_busy+zte_list[0])

df_zte_5G_traffic=pd.merge(df_zte_5G_traffic,df_enodeb_name,how='left',on='基站ID+小区ID')
df_zte_5G=pd.merge(df_zte_5G_traffic,df_zte_5G_traffic_busy,how='left',on='标记')

df_zte_5G=df_zte_5G[['地市','厂家','基站ID+小区ID','区县','基站名称','日期','PLMN','RRC最大连接用户数','上行RLC层用户面总流量(GB)','下行RLC层用户面总流量(GB)','总流量','PRB利用率']]

df_zong=pd.concat([df_eric_5G2,df_zte_5G],ignore_index=True)
df_zong.fillna(0,inplace=True)

with pd.ExcelWriter(path_out+'5G周报.xlsx') as f:
    df_zong.to_excel(f, index =False)