智能云柜 报表 SmartReport

# coding=utf-8
import datetime
from os import path, getcwd, system
from sys import exit
from time import sleep

import pandas as pd
from plotly.subplots import make_subplots
import plotly.graph_objs as go
from plotly.io import write_image
from pandas import read_excel
import PySimpleGUI as sg


def File_choice():
    sg.theme('LightGreen3')
    layout = [sg.Frame('文件选择:', [
        [sg.FileBrowse(' 请选择文件:'), sg.Input('', size=(60, 1), key='_FILEPATH_')],
        [sg.Button(button_text='  确定  ', key='Ok', size=(6, 1), bind_return_key=True),
         sg.Button(button_text='  退出  ', key='Exit', size=(6, 1))],
    ], element_justification='center')]
    window = sg.Window('文件选择', [layout])
    while True:
        event, values = window.read()
        if event in ('Exit', sg.WIN_CLOSED):
            window.close()
            break
        if event == 'Ok':
            FILE_PATH = values['_FILEPATH_']
            if path.exists(FILE_PATH):
                return FILE_PATH
                window.close()
                break
            else:
                sg.popup_auto_close('所选文件不存在!', auto_close_duration=2)

def ReadXLS(PATH='', SheetName=''):
    data = read_excel(PATH, sheet_name=SheetName, header=0, engine='openpyxl')  # 读取excel,Sheetname默认"Table"
    END = data['操作日期'].dt.strftime('%Y/%m/%d').values[0]
    START = data['操作日期'].dt.strftime('%Y/%m/%d').values[-1]
    data['商品规格'].fillna('None', inplace=True)
    data = data.groupby(by=['商品编码', '商品名称', '工单号', '工单数量', '商品规格', '图号'], as_index=False).agg(
        {'每盒数量': 'sum', '刀具已加工产品数量': 'sum', '是否已还': lambda x: x[x == '已还'].count()}, index=False)
    data['寿命'] = (data['刀具已加工产品数量'] / data['是否已还'] / 100).round() * 100  # 100整倍数
    data['工单总数'] = ''
    data = data.groupby(by=['商品编码', '商品名称', '商品规格', '图号'], as_index=False) \
        .agg({'工单号': 'nunique', '工单数量': 'sum', '每盒数量': 'sum', '刀具已加工产品数量': 'sum', '是否已还': 'sum'}, index=False)
    data.sort_values('每盒数量', inplace=True, ascending=False)
    data['占比'] = data['每盒数量'].cumsum() / data['每盒数量'].sum()
    # data['占比'] = data['占比'].apply(lambda x:format(x,'.1%'))
    data['平均寿命'] = (data['刀具已加工产品数量'] / data['是否已还'] / 100).round() * 100

    # dataNew = data.groupby(by=['商品编码', '商品名称', '商品规格'], as_index=False).agg({'每盒数量': 'sum'})
    # dataNew.sort_values('每盒数量', inplace=True, ascending=False)
    # dataNew['占比'] = dataNew['每盒数量'].cumsum() / dataNew['每盒数量'].sum()

    LISTS = []
    for CutterPartNum in data['商品编码']:
        x = data[data['商品编码'] == CutterPartNum]['每盒数量'].sum()
        LISTS.append(x)
    data['刀具领用数2'] = LISTS
    return data,START,END

def CreateCharts(df,START,END,CURR_PATH):
    TOTAL = df['每盒数量'].sum()
    title1 = '未归还\已归还数量'
    title2 = '共领用%s款刀具,合计总数%sEA' % (df['商品编码'].nunique(), TOTAL)
    title4 = '刀具对应平均寿命'
    title5 = '刀具对应领用总数  __共%s款,总数%sEA__' % (df['商品编码'].nunique(), df['每盒数量'].sum())
    RETURNDED = df['是否已还'].sum()
    NOT_RETURN = df['每盒数量'].sum()-RETURNDED

    DATA_PIE_TOTAL = df.groupby(by=['商品编码', '商品名称', '商品规格'], as_index=False).agg({'每盒数量': 'sum'})
    DATA_PIE_TOTAL.sort_values('每盒数量', inplace=True, ascending=False)
    DATA_PIE_TOTAL['占比'] = DATA_PIE_TOTAL['每盒数量'].cumsum() / DATA_PIE_TOTAL['每盒数量'].sum()

    Percant80 = DATA_PIE_TOTAL[DATA_PIE_TOTAL['占比'] < 0.8]
    BOTTOM20 = DATA_PIE_TOTAL[DATA_PIE_TOTAL['占比'] >= 0.8]
    OTHER20_CLASS = BOTTOM20.shape[0]
    OTHER20_PERCANT = 1-Percant80.iloc[-1,-1]
    OTHER20_SUM = BOTTOM20['每盒数量'].sum()

    Percant80 = Percant80.append({'商品编码': '其它%s款' % OTHER20_CLASS, '商品名称': '', '商品规格': '', '每盒数量': OTHER20_SUM, '占比': OTHER20_PERCANT},ignore_index=True)
    # Percant80['占比2'] = Percant80['每盒数量'] / Percant80['每盒数量'].sum()
    df2 = df.groupby(by=['商品编码','商品名称','商品规格'],as_index=False).agg({'每盒数量':'sum'})
    df2.sort_values('每盒数量',inplace=True,ascending=False)
    df2['累积占比'] = df2['每盒数量'].cumsum()/df2['每盒数量'].sum()
    df3 = df2[df2['累积占比']<=0.8]
    if df2.shape[0]>50:
        df2 = df2.iloc[0:50,:]

    LENGTH = df.shape[0]
    if LENGTH > 50:
        TITLE = '每款产品的领刀数量 _Top 50 of %s组_'%LENGTH
        df = df.iloc[0:50,:]
    elif LENGTH <= 50:
        TITLE = '每款产品的领刀数量 _共%s组_'%LENGTH
    title3 = TITLE

    PIE_SUM = go.Pie(values=[RETURNDED, NOT_RETURN],
                     labels=['已归还数量', '未归还数量'],
                     hole=0.5,
                     textinfo='label+value+percent',
                     marker = {'colors': ['#7CCD7C', '#FF4040']},
                     showlegend=False,
                     )

    PIE_TOTAL = go.Pie(
        values=Percant80['每盒数量'],
        labels=Percant80['商品编码'],
        hole=0.5,
        textinfo='label+value+percent',
        sort=False,
        direction='clockwise',
        showlegend=False,
    )

    BAR_CLASS = go.Bar(
        x=df['商品编码']+','+df['图号'].astype('str'),
        y=df['每盒数量'],
        text=df['每盒数量'],
        textfont_size=30,
        textposition='outside',
    )

    BAR_LIFE = go.Bar(
        x=df['商品编码']+','+df['图号'].astype('str'),
        y=df['平均寿命'],
        text=df['平均寿命'],
        textfont=dict(size=30,color='white'),
        textangle=270,
        textposition='inside',
        showlegend=False,
    )

    SCATTER_CLASS = go.Scatter(
        x = df['商品编码'] + ',' + df['图号'].astype('str'),
        y = df['每盒数量'].cumsum()/TOTAL,
        mode='lines',
        showlegend=False,
    )

    BAR_TOTAL = go.Bar(
        x=df2['商品编码']+','+df2['商品名称'],
        y=df2['每盒数量'],
        text=df2['每盒数量'],
        textfont_size=20,
        textposition='outside',
        showlegend=False,
    )

    SCATTER_TOTAL = go.Scatter(
        x=df2['商品编码']+','+df2['商品名称'],
        y = df2['累积占比'],
        marker={'size': 6},
        line={'width': 2},
        mode='lines',
        name='各款刀各图号累积占比',
        # text=df2['累积占比'].apply(lambda x:format(x,'.0%')),
        textposition='top center',
        textfont={'size': 8},
        showlegend=False,
    )

    SCATTER_TOTAL2 = go.Scatter(
        x=[df3.iloc[-1,:]['商品编码']+','+df3.iloc[-1,:]['商品名称']],
        y = [df3.iloc[-1,:]['累积占比']],
        marker={'size': 12,'color':'red','symbol':'circle-dot'},
        text=[format(df3.iloc[-1,:]['累积占比'],'.0%')],
        mode='markers+text',
        name='各款刀各图号累积占比',
        textposition='top center',
        textfont={'size': 12,'color':'red'},
        showlegend=False,
    )

    fig = make_subplots(
        specs=[
            [{'type': 'pie'}, {'type': 'pie'}],
            [{'type': 'xy', 'secondary_y': True, 'colspan': 2}, None],
            [{'type': 'bar', 'colspan': 2}, None],
            [{'type': 'xy', 'secondary_y': True, 'colspan': 2}, None]
        ],
        subplot_titles=[title1,title2,title3,title4,title5],
        rows=4,
        cols=2)
    fig.add_trace(PIE_SUM,row=1,col=1)
    fig.add_trace(PIE_TOTAL,row=1,col=2)
    fig.add_trace(BAR_CLASS,row=2,col=1)
    fig.add_trace(SCATTER_CLASS,secondary_y=True,row=2,col=1)
    fig.add_trace(BAR_LIFE,row=3,col=1)
    fig.add_trace(BAR_TOTAL,row=4,col=1)
    fig.add_trace(SCATTER_TOTAL,secondary_y=True,row=4,col=1)
    fig.add_trace(SCATTER_TOTAL2, secondary_y=True, row=4, col=1)
    fig.update_xaxes({'tickfont': {'size': 15}})
    fig.update_layout(margin=dict(t=60, l=10, r=10, b=40, pad=1),
                      template='ggplot2',
                      title='<b>刀具柜统计报表</b><br>%s~%s'%(START,END),
                      title_font_size=30,#={'size': 22},# 'color': 'rgb(153,51,0)'},
                      showlegend=False,
                      xaxis1_tickangle=45,
                      xaxis2_tickangle=45,
                      xaxis3_tickangle=45,
                      title_x=0.5)
    write_image(fig,r'%s\刀具柜统计_%s~%s.png' % (CURR_PATH, START.replace('/', ''), END.replace('/', '')),width=1360,height=2000,scale=2)
    fig.write_html(r'd:\刀柜统计.html',default_width=1360,default_height=2400)


def main():
    XLS_PATH = File_choice()
    CURR_PATH = path.dirname(XLS_PATH)
    if XLS_PATH:
        df, START, END = ReadXLS(XLS_PATH, SheetName='Table')
        df.to_excel(r'%s\刀具柜统计_%s~%s.xlsx' % (CURR_PATH, START.replace('/', ''), END.replace('/', '')),engine='openpyxl',encoding='utf-8', index=False)  # 存入excel
        CreateCharts(df, START, END,CURR_PATH)

if __name__ == '__main__':
    main()