123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214 |
- '''
- 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)
|