pywinment/winmentor/mentorutils.py

'''
Created on Jul 12, 2018 @author: levente.marton
'''
# from . import WinMentor
import xlsxwriter as xlw
from xlsxwriter.worksheet import Worksheet as sheet_
from .dbread import WinMentor

class Mentorutils(WinMentor):
    '''
    winmentor subclass to generate xlsx situations
    '''

    def __init__(self):  # , winment_path='c:/winment/data/'):
        '''
        Constructor
        '''
        # self.winment_path = winment_path
        self.row, self.col = 3, 0
        super().__init__()

    def gen_txt(self, db_file='/firme.db', file_name=None, attr_index=1, name=None):
        self.db_file = db_file
        self.file_name = file_name
        self.attr_index = attr_index
        if file_name:
            with open(file_name, 'w') as my_file:
                my_file.write(''.join('{0},\n'.format(_row) for _row in self.verif_corp(db_file, name) if attr_index == 'all'))
                my_file.write(''.join('{0},\n'.format(_row[attr_index]) for _row in self.verif_corp(db_file, name)))
            return file_name
        elif attr_index == 'all':
            return [_row for _row in self.verif_corp(db_file, name)]
        else:
            return [_row[attr_index] for _row in self.verif_corp(db_file, name) if _row[attr_index] is not None]

    def _preformat_wb(self, xl_name, w_sheet_name, n_headers=[], n_headers2=[]):
        dest_book = xlw.Workbook(xl_name + '.xlsm')
        dest_book.add_vba_project('./vbaProject.bin')
        sheet_ = dest_book.add_worksheet(w_sheet_name)
        sheet2_ = dest_book.add_worksheet('to')
        head_format = dest_book.add_format({'bold': True, 'text_wrap': True})
        num_format = dest_book.add_format()
        num_format.set_num_format('#,##0.00')
        sheet_.autofilter(self.row - 1, 0, 100, len(n_headers) - 1)
        sheet_.freeze_panes(self.row, 0)
        sheet2_.autofilter(self.row - 1, 0, 100, len(n_headers) - 1)
        sheet2_.freeze_panes(self.row, 0)
        hcol = 0
        for header in n_headers:
            sheet_.write(self.row - 1, hcol, header, head_format)
            hcol += 1
        hcol = 0
        for header in n_headers2:
            sheet2_.write(self.row - 1, hcol, header, head_format)
            hcol += 1
        return (sheet_, sheet2_, dest_book, num_format)

    def gen_turnover(self, xl_name, w_sheet_name, seq, m_month=None, n_headers=[]):
        sheet_ = self._preformat_wb(xl_name, w_sheet_name, n_headers)
        sheet_[0].set_column('L:Q', 11, cell_format=sheet_[2])
        sheet_[0].set_column('A:A', 20)
        sheet_[0].set_column('B:B', 11)
        for company in sorted(seq):
            if m_month:
                if m_month >= self.get_last_month(company[6]):
                    current_month = self.get_last_month(company[6])
                else:
                    current_month = m_month
            else:
                current_month = self.get_last_month(company[6])
            company.append(self.an_inc(self.verif_cont(company[6] + '/' + current_month + './NCONT.DB'), 0, 6, 8))
            company.append(self.result(self.verif_cont(company[6] + '/' + current_month + './NCONT.DB'), 0, 6, 8))
            company.append(self.divid(self.verif_cont(company[6] + '/' + current_month + './NCONT.DB')))
            company.append(self.divid_current(self.verif_cont(company[6] + '/' + current_month + './NCONT.DB')))
            company.append(self.divid_intermed(self.verif_cont(company[6] + '/' + current_month + './NCONT.DB')))
            company.append(self.divid_inter_current(self.verif_cont(company[6] + '/' + current_month + './NCONT.DB')))
            company.append(self.spons(self.verif_cont(company[6] + '/' + current_month + './NCONT.DB')))
            company.append(current_month)
            sheet_[0].write_row(self.row, self.col, company,)
            self.row += 1
            print('Processing:', company[0])
        print('_done_')
        sheet_[1].close()

    def gen_tax_payable(self, xl_name, w_sheet_name, seq, m_month=None, n_headers=[], n_headers2=[], firm_list=False):
        sheet_ = self._preformat_wb(xl_name, w_sheet_name, n_headers, n_headers2)
        # sheet2_ = self._preformat_wb(xl_name, w_sheet_name, n_headers2)
        sheet_[0].set_column('L:U', 11, cell_format=sheet_[3])
        sheet_[0].set_column('A:A', 20)
        sheet_[0].set_column('B:B', 11)
        sheet_[1].set_column('L:Q', 11, cell_format=sheet_[3])
        sheet_[1].set_column('A:A', 20)
        sheet_[1].set_column('B:B', 11)
        # seq2 = seq.copy()
        if not firm_list:
            sheet_[0].set_column('D:J', None, None, {'hidden': True})
            for company in sorted(seq):
                if m_month:
                    if m_month >= self.get_last_month(company[6]):
                        current_month = self.get_last_month(company[6])
                    else:
                        current_month = m_month
                else:
                    current_month = self.get_last_month(company[6])
                company.append(self.vat_payable(self.verif_cont(company[6] + '/' + current_month + './NCONT.DB')))
                company.append(self.vat_final(self.verif_cont(company[6] + '/' + current_month + './NCONT.DB')))
                company.append(self.tax_payable(self.verif_cont(company[6] + '/' + current_month + './NCONT.DB')))
                company.append(self.div_tax_payable(self.verif_cont(company[6] + '/' + current_month + './NCONT.DB')))
                company.append(self.CAS_payable(self.verif_cont(company[6] + '/' + current_month + './NCONT.DB')))
                company.append(self.CASS_payable(self.verif_cont(company[6] + '/' + current_month + './NCONT.DB')))
                company.append(self.sal_tax_payable(self.verif_cont(company[6] + '/' + current_month + './NCONT.DB')))
                company.append(self.cam_payable(self.verif_cont(company[6] + '/' + current_month + './NCONT.DB')))
                company.append(self.an_inc(self.verif_cont(company[6] + '/' + current_month + './NCONT.DB'), 0, 6, 8))
                company.append(self.result(self.verif_cont(company[6] + '/' + current_month + './NCONT.DB'), 0, 6, 8))
                company.append(current_month)
                sheet_[0].write_row(self.row, self.col, company,)
                self.row += 1
                print('Processing:', company[0], '>> taxes')
            # seq2 = sorted(seq[:11])
            self.row = 3
            sheet_[1].set_column('D:J', None, None, {'hidden': True})
            for company in sorted(seq):
                if m_month:
                    if m_month >= self.get_last_month(company[6]):
                        current_month = self.get_last_month(company[6])
                    else:
                        current_month = m_month
                else:
                    current_month = self.get_last_month(company[6])
                company = company[:11]
                # company.append(self.an_inc(self.verif_cont(company[6] + '/' + current_month + './NCONT.DB'), 0, 6, 8))
                # company.append(self.result(self.verif_cont(company[6] + '/' + current_month + './NCONT.DB'), 0, 6, 8))
                company.append(self.divid(self.verif_cont(company[6] + '/' + current_month + './NCONT.DB')))
                company.append(self.divid_current(self.verif_cont(company[6] + '/' + current_month + './NCONT.DB')))
                company.append(self.divid_intermed(self.verif_cont(company[6] + '/' + current_month + './NCONT.DB')))
                company.append(self.divid_inter_current(self.verif_cont(company[6] + '/' + current_month + './NCONT.DB')))
                company.append(self.spons(self.verif_cont(company[6] + '/' + current_month + './NCONT.DB')))
                company.append(self.advance_final(self.verif_cont(company[6] + '/' + current_month + './NCONT.DB')))
                company.append(self.deb_div(self.verif_cont(company[6] + '/' + current_month + './NCONT.DB')))
                company.append(self.credit_final(self.verif_cont(company[6] + '/' + current_month + './NCONT.DB')))
                company.append(current_month)
                sheet_[1].write_row(self.row, self.col, company,)
                self.row += 1
                print('Processing:', company[0], ' >> indicators')
                # print('_done_')
        else:
            for company in sorted(seq):
                sheet_[0].write_row(self.row, self.col, company,)
                self.row += 1
                print('Processing:', company[0])
        print('_done_')
        sheet_[2].close()

    def gen_society(self, xl_name, w_sheet_name, seq, society, n_headers=[], range_year=None, range_month=None):
        sheet_ = self._preformat_wb(xl_name, w_sheet_name, n_headers)
        sheet_[0].set_column('L:U', 11, cell_format=sheet_[2])
        sheet_[0].set_column('A:A', 20)
        sheet_[0].set_column('B:B', 11)
        sheet_[0].set_column('D:J', None, None, {'hidden': True})
        for year_ in range(int(range_year[:4]), int(range_year[-4:]) + 1):
            for month_ in range(int(range_month[:2]), int(range_month[-2:]) + 1):
                for company in sorted(seq):
                    # print(company)
                    if company[6] == society:
                        company = company[:11]
                        company.append(self.vat_payable(self.verif_cont(company[6] + '/' + '{}_{:02d}'.format(year_, month_) + './NCONT.DB')))
                        company.append(self.vat_final(self.verif_cont(company[6] + '/' + '{}_{:02d}'.format(year_, month_) + './NCONT.DB')))
                        company.append(self.tax_payable(self.verif_cont(company[6] + '/' + '{}_{:02d}'.format(year_, month_) + './NCONT.DB')))
                        company.append(self.div_tax_payable(self.verif_cont(company[6] + '/' + '{}_{:02d}'.format(year_, month_) + './NCONT.DB')))
                        company.append(self.CAS_payable(self.verif_cont(company[6] + '/' + '{}_{:02d}'.format(year_, month_) + './NCONT.DB')))
                        company.append(self.CASS_payable(self.verif_cont(company[6] + '/' + '{}_{:02d}'.format(year_, month_) + './NCONT.DB')))
                        company.append(self.sal_tax_payable(self.verif_cont(company[6] + '/' + '{}_{:02d}'.format(year_, month_) + './NCONT.DB')))
                        company.append(self.cam_payable(self.verif_cont(company[6] + '/' + '{}_{:02d}'.format(year_, month_) + './NCONT.DB')))
                        company.append(self.an_inc(self.verif_cont(company[6] + '/' + '{}_{:02d}'.format(year_, month_) + './NCONT.DB'), 0, 6, 8))
                        company.append(self.result(self.verif_cont(company[6] + '/' + '{}_{:02d}'.format(year_, month_) + './NCONT.DB'), 0, 6, 8))
                        company.append(year_)
                        company.append(month_)
                        sheet_[0].write_row(self.row, self.col, company)
                        self.row += 1
                        print('Processing:', company[0], '{}_{}'.format(year_, month_))
        sheet_[1].close()

    def gen_sal(self, xl_name, w_sheet_name, seq, m_month=None, n_headers=[]):
        sheet_ = self._preformat_wb(xl_name, w_sheet_name, n_headers)
        for company in sorted(seq):
            # print(company)
            if m_month:
                if m_month >= self.get_last_month(company[6]):
                    current_month = self.get_last_month(company[6])
                else:
                    current_month = m_month
            else:
                current_month = self.get_last_month(company[6])
            sal_list = self.make_sal_list(company[6] + './NPERS.DB', company[6] + '/' + current_month + './LIKISAL.DB')
            print(sal_list)
            for employee in sal_list:
                company.extend(employee)
                company.append(current_month)
                sheet_[0].write_row(self.row, self.col, company,)
                self.row += 1
                del company[11:]
        sheet_[0].write_formula(1, 0, '=SUMPRODUCT(1/COUNTIF(A5:A1000,A5:A1000 & ""))-1')
        sheet_[0].set_column('G:K', None, None, {'hidden': True})
        sheet_[2].close()


# mentor = WinMentor()
# utils = Mentorutils()

# utils.gen_turnover('turn_over', 'to', mentor.verif_corp('/firme.db'), m_month='2018_06', n_headers=['Denumire', 'CF', 'J', 'Adresa', 'Oras', 'Judet',
#                                                                                                 'Prescurtat', 'Obs', 'TurnO', 'Result', 'month'])
# utils.gen_tax_payable('taxes', 'tx', mentor.verif_corp('/firme.db'), m_month='2018_06', n_headers=['Denumire', 'CF', 'J', 'Adresa', 'Oras', 'Judet',
#                                                                                                 'Prescurtat', 'Obs', 'Vat', 'Ins' , 'Tax', 'Sal_tax', 'month'])
# utils.gen_txt('/firme.db', 'lista_cf.txt', 1)