Pandas report

products = ('BILLETS', 'REBARS', 'WIRE ROD', 'SECTION', 'PIG IRON','REBARS In Coils','REBARS Random')
SUCCESS_COLOR = utl.colorama.Fore.LIGHTGREEN_EX
#===============================================================================
class Bot(cmo.RoBot):
    #---------------------------------------------------------------------------
    def do(self, data_filename, export_pattern_filename, rest_pattern_filename):
        datapath = '.\\data\\'
        echo.warning('Сформировать отчеты по заказам(Booking):')
        echo.info(f'Загрузить {data_filename}', dotLine=True, terminator='')
        with warnings.catch_warnings():
            warnings.filterwarnings("ignore", category=PendingDeprecationWarning)
            warnings.filterwarnings("ignore", category=DeprecationWarning)        
            df = pd.read_excel(data_filename, sheet_name='data', usecols='A:K')
        echo.info('OK', colorText=SUCCESS_COLOR)
 
        echo.info('Объеденить заказы с одинаковой ценой, и взять только Prime металл и чугун')
        df.columns = ['RegionBD', 'PCate', 'PType', 'Country name', 'Addendum #', 'FOB index', 'FOB Price', 'FOB Amount', 'week #', 'Book. Date', 'SO qty']
 
        mask = (df['PType'].isin(['Prime', 0]))
        df = df[mask]
        df['PCate'] = df['PCate'].apply(setPcate)
        df['SO qty'] = df['SO qty'] / 1000
 
        excel.init()
        self.make_daily_export_report(df, 'Book. Date', export_pattern_filename, datapath)
        self.make_daily_rest_report(df, 'Book. Date', rest_pattern_filename, datapath)
        excel.quit()
 
    #---------------------------------------------------------------------------
    def make_daily_rest_report1(self, df, period, pattern_filename, save_to):
        echo.warning('Сформировать посуточный отчет по заказам для CIS, Russia и Ukraine')
        # Отобрать только не экспортные заказы
        mask = df['RegionBD'].isin(['CIS', 'Russia', 'Ukraine'])
        df = df[mask]
        # Объеденить те заказы у которых FOB Price одинаковый
        df['FOB Amount'] = df['FOB Price'] * df['SO qty']
        df = df.groupby(['PCate', period, 'Country name']).agg({'FOB Amount': 'sum', 'SO qty': 'sum', 'FOB Price': 'sum'}).reset_index()
        df['FOB Price'] = df['FOB Amount'] / df['SO qty']
 
        df = df[['PCate', period, 'Country name', 'SO qty', 'FOB Price']].sort_values([period, 'PCate', 'Country name'])
        cards = self.raw_df_to_cards(df, add_diff=False)
        matrix = self.cards_to_matrix(cards, filler = ['', '', ''])
        wb, ws = self.make_report(matrix, pattern_filename)
 
        filename = f"Daily Booking Ukr., CIS, Ru.{datetime.date.today().strftime('%Y-%m-%d')}(a).xlsx"
        echo.info('Сохранить файл ' + filename)
        wb.save(save_to + filename)
        wb.close()
 
    #---------------------------------------------------------------------------
    def make_daily_rest_report(self, df, period, pattern_filename, save_to):
        echo.warning('Сформировать посуточный отчет по заказам для CIS, Russia и Ukraine')
        # Отобрать только не экспортные заказы
        mask = df['RegionBD'].isin(['CIS', 'Russia', 'Ukraine'])
        df = df[mask]
        df['EXW Price'] = df['FOB Price']
        df['FOB Price'] = df['FOB Price'] + 20
 
        df['EXW Amount'] = df['EXW Price'] * df['SO qty']
        df['FOB Amount'] = df['FOB Price'] * df['SO qty']
        df['FOB index Amount'] = df['FOB index'] * df['SO qty']
 
        df = df.groupby(['PCate', period, 'Country name']).agg({
            'SO qty': 'sum',
            'FOB Price': 'sum',
            'FOB Amount': 'sum',
            'EXW Price': 'sum', 
            'EXW Amount': 'sum', 
            'FOB index': 'sum', 
            'FOB index Amount': 'sum'}
        ).reset_index()
        df['FOB Price'] = df['FOB Amount'] / df['SO qty']
        df['FOB index'] = df['FOB index Amount'] / df['SO qty']
        df['EXW Price'] = df['EXW Amount'] / df['SO qty']
 
        df = df[['PCate', period, 'Country name', 'SO qty', 'EXW Price', 'FOB Price', 'FOB index']].sort_values([period, 'PCate', 'Country name'])
 
        cards = self.raw_df_to_cards(df, calc_diff=(5,6))
        matrix = self.cards_to_matrix(cards, filler = ['', '', '', '', '', ''])
        wb, ws = self.make_report(matrix, pattern_filename)
 
        filename = f"Daily Booking Ukr., CIS, Ru.{datetime.date.today().strftime('%Y-%m-%d')}(a).xlsx"
        echo.info('Сохранить файл ' + filename)
        wb.save(save_to + filename)
        wb.close()
 
 
    #---------------------------------------------------------------------------
    def make_daily_byweeks_rest_report(self, df, period, pattern_filename, save_to):
        echo.warning('Сформировать понедельный отчет по заказам для CIS, Russia и Ukraine')
        # Отобрать только не экспортные заказы
        mask = df['RegionBD'].isin(['CIS', 'Russia', 'Ukraine'])
        df = df[mask]
        # Объеденить те заказы у которых FOB Price одинаковый
        df['FOB Amount'] = df['FOB Price'] * df['SO qty']
        df = df.groupby(['PCate', period, 'Country name']).agg({'FOB Amount': 'sum', 'SO qty': 'sum', 'FOB Price': 'sum'}).reset_index()
        df['FOB Price'] = df['FOB Amount'] / df['SO qty']
        df = df[['PCate', period, 'Country name', 'SO qty', 'FOB Price']].sort_values([period, 'PCate', 'Country name'])
 
        cards = self.raw_df_to_cards(df, add_diff=False)
        matrix = self.cards_to_matrix(cards, filler = ['', '', ''])
        wb, ws = self.make_report(matrix, pattern_filename, reset_first_row_format=True)
 
        filename = f"Daily(by weeks) Booking Ukr., CIS, Ru.{datetime.date.today().strftime('%Y-%m-%d')}(a).xlsx"
        echo.info('Сохранить файл ' + filename)
        wb.save(save_to + filename)
        wb.close()
 
    #---------------------------------------------------------------------------
    def make_daily_export_report(self, df, period, pattern_filename, save_to):
        echo.warning('Сформировать посуточный отчет по заказам для экспорта')
        # Отобрать только экспортные заказы
        mask = df['RegionBD'].isin(['CIS', 'Russia', 'Ukraine'])
        df = df[~mask]
        # Объеденить те заказы у которых FOB Price одинаковый
        df = df.groupby(['PCate', period, 'Country name', 'FOB Price']).agg({'FOB index': 'first', 'Addendum #': 'first', 'SO qty': 'sum'}).reset_index()
        df = df[['PCate', period, 'Country name', 'SO qty', 'FOB Price', 'FOB index']].sort_values([period, 'PCate', 'Country name'])
 
        cards = self.raw_df_to_cards(df, calc_diff=(4,5))
        matrix = self.cards_to_matrix(cards, filler = ['', '', '', '', ''])
        wb, ws = self.make_report(matrix, pattern_filename)
 
        filename = f"Daily Booking Export.{datetime.date.today().strftime('%Y-%m-%d')}(a).xlsx"
        echo.info('Сохранить файл ' + filename)
        wb.save(save_to + filename)
        wb.close()
 
    #---------------------------------------------------------------------------
    def make_daily_byweeks_export_report(self, df, period, pattern_filename, save_to):
        echo.warning('Сформировать понедельный отчет по заказам для экспорта')
        # Отобрать только экспортные заказы
        mask = df['RegionBD'].isin(['CIS', 'Russia', 'Ukraine'])
        df = df[~mask]
        # Объеденить те заказы у которых FOB Price одинаковый
        df = df.groupby(['PCate', period, 'Country name', 'FOB Price']).agg({'FOB index': 'first', 'Addendum #': 'first', 'SO qty': 'sum'}).reset_index()
        df['FOB Amount'] = df['FOB Price'] * df['SO qty']
        df = df.groupby(['PCate', period, 'Country name']).agg({'FOB Amount': 'sum', 'SO qty': 'sum', 'FOB Price': 'sum', 'FOB index': 'mean'}).reset_index()
        df['FOB Price'] = df['FOB Amount'] / df['SO qty']
        df = df[['PCate', period, 'Country name', 'SO qty', 'FOB Price', 'FOB index']].sort_values([period, 'PCate', 'Country name'])
 
        cards = self.raw_df_to_cards(df, calc_diff=(4,5))
        matrix = self.cards_to_matrix(cards, filler = ['', '', '', '', ''])
        wb, ws = self.make_report(matrix, pattern_filename, reset_first_row_format=True)
 
        filename = f"Daily(by weeks) Booking Export.{datetime.date.today().strftime('%Y-%m-%d')}(a).xlsx"
        echo.info('Сохранить файл ' + filename)
        wb.save(save_to + filename)
        wb.close()
 
    #---------------------------------------------------------------------------
    def make_report(self, matrix, pattern_filename, reset_first_row_format = False):
        req_columns = len(matrix)
        matrix = self.pivot_matrix(matrix)
        have_columns = 2
        start_column = 3
        end_row = len(matrix) + 1
 
        echo.info('Загрузить файл-шаблон', dotLine=True, terminator='')
        wb = excel.app.books.open(pattern_filename)
        echo.info('OK', colorText=SUCCESS_COLOR)
 
        ws = wb.sheets.active
        # Добавить недостающие столбцы
        insertNewColumns(ws, (1, start_column), (end_row, start_column + req_columns - have_columns))
        # Вставить данные
        ws.range((1, start_column)).value = matrix
        table = ws.range((1, start_column), (end_row, start_column + req_columns))
        # Подогнать ширину столбцов
        table.autofit()    
        # Добавить условное форматирование для разделения дней верт. линиями
        fc = table.api.FormatConditions.Add(
            xlconstants.FormatConditionType.xlExpression, 
            xlconstants.FormatConditionOperator.xlEqual, 
            "=B$1<>C$1")
        borders = fc.Borders(xlconstants.Constants.xlLeft)
        borders.LineStyle = xlconstants.LineStyle.xlContinuous
        fc.StopIfTrue = False
        # Установить дату отчета
        ws.range("A1").value = datetime.date.today() + datetime.timedelta(days=-1)
 
        if reset_first_row_format:
            ws.range((1, start_column), (1, start_column + req_columns)).api.NumberFormat = "General"
        return wb, ws
 
    #---------------------------------------------------------------------------
    def raw_df_to_cards(self, df, calc_diff=None):
        cards = {}
        for _, values in df.iterrows():
            card = values.to_list()
            dat = card[1]
            product = card[0]
            if calc_diff is not None:
                card.append(round(card[calc_diff[0]] - card[calc_diff[1]], 2))
            if dat not in cards:
                cards[dat] = {}
            if product not in cards[dat]:
                cards[dat][product] = []
            cards[dat][product].append(card[2:])
        return cards
 
    #---------------------------------------------------------------------------
    def cards_to_matrix(self, cards, filler):
        matrix = []
        for dat in cards:
            is_empty= [False]*len(products)
            while not all(is_empty):
                column = [dat]
                for product in products:
                    if product in cards[dat]:
                        if len(cards[dat][product])>0:
                            column += cards[dat][product].pop(0)
                        else:
                            column += filler
                        is_empty[products.index(product)] = (len(cards[dat][product]) == 0)
                    else:
                        column += filler
                        is_empty[products.index(product)] = True
                matrix.append(column)
        return matrix
 
    #---------------------------------------------------------------------------
    def pivot_matrix(self, matrix):
        new_matrix = []
        for j in range(len(matrix[0])):
            row = []
            for i in range(len(matrix)):
                row.append(matrix[i][j])
            new_matrix.append(row)            
        return new_matrix
 
    #---------------------------------------------------------------------------
    def df_to_cards(self, df):
        cards = {}
 
        for i in products:
            idf = df.loc[(i, )]
 
            for dat, values in idf.iteritems():
                if dat not in cards:
                    cards[dat] = {}
                if i not in cards[dat]:
                    cards[dat][i] = []
 
                card = values.to_list()
                if card[1] > 0:
                    cards[dat][i].append(card)
        return cards
 
    #---------------------------------------------------------------------------
    def add_variance_country(self, df, values):
        idx = max(df.index.get_level_values('order')) + 1
        (names, _) = list(zip(*values))
 
        for i in products:
            df.loc[(i, 'Variance', idx)] = df.loc[(i, 'FOB Price', names.index('FOB Price')+1 )] - df.loc[(i, 'FOB index', names.index('FOB index')+1)]
            adf = df.loc[(i, 'FOB Price', names.index('FOB Price')+1)]
            countries = adf.index.get_level_values('Country name')
            df.loc[(i, 'Country', 0)] = countries
        df = df.droplevel('Country name', axis=1)
        return df.sort_values(['PCate', 'order'])
 
    #---------------------------------------------------------------------------
    def getPivots(self, df, values):
        res = []
        idx = 1
        for (name, func) in values:
            grouped = pd.pivot_table(df, values=name, index=['PCate', 'Addendum #'], columns=['Book. Date', 'Country name'], aggfunc=func)
            grouped = grouped.droplevel('Addendum #', axis=0).reset_index()
            grouped['kind'] = name
            grouped['order'] = idx
            grouped = grouped.groupby(['PCate', 'kind', 'order']).sum()
            res.append(grouped.copy())
            idx +=1
        df = pd.concat(res)
        return df.sort_values(['PCate', 'order'])
 
    #---------------------------------------------------------------------------
    def results_to_html(self):
        ret = ''
        return ret
 
 
#---------------------------------------------------------------------------
def fobIndex_to_num(value):
    if isinstance(value, str):
        if '(пусто)' in value:
            return 0
        else:
            return float(value)
    else:
        return value    
 
#---------------------------------------------------------------------------
def insertNewColumns(ws: excel.xw.Sheet, c1: Tuple[int, int], c2: Tuple[int, int]) -> None:
    '''Вставить в лист пустые столбцы, скопировав формат первого столбца на все остальные столбцы
    '''
    sRange = ws.range((c1[0], c1[1]+1), c2)
    sRange.api.Insert(xlconstants.InsertShiftDirection.xlShiftToRight,
            CopyOrigin=xlconstants.InsertFormatOrigin.xlFormatFromLeftOrAbove)
    nRange = ws.range(c1, c2)
    nRange.api.FillRight()
 
#---------------------------------------------------------------------------
def setPcate(pcate):
    for i in products:
        if i in pcate:
            return i
    raise Exception(f'Неизвестное значение PCate: {pcate}')
        
#---------------------------------------------------------------------------
def getFilename(id):
    if len(sys.argv) > id:
        pid = sys.argv[id]
        return(pid)
    else:
        echo.error(f'Error: booking filename is needed')
        sys.exit(1)
 
#---------------------------------------------------------------------------
def getTemp():
    if utl.isRemotePC():            
        temp = utl.getRemoteShare() + 'temp\\'
    else:                
        temp = os.path.dirname(os.path.abspath(__file__)) + '\\temp\\'
    return temp
 
#---------------------------------------------------------------------------
def closeScript(event):
    sapBot.finished_with_errors(Exception('Выполнение прервано пользователем.'))
    utl.unset_exit_handler(closeScript)
    sys.exit(1)