''' Created on Jul 12, 2018 @author: levente.marton ''' # from . import WinMentor import xlsxwriter as xlw # type: ignore from xlsxwriter.worksheet import Worksheet as sheet_ # type: ignore 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.other_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.advance_revenue(self.verif_cont(company[6] + '/' + current_month + './NCONT.DB'))) 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)