From 47130b2dfb2d2489b0dc825d43fe31c2644ac0b6 Mon Sep 17 00:00:00 2001 From: Tom Marble Date: Sat, 8 Sep 2012 00:30:37 -0500 Subject: First pass technical study of creating ODS from ledger --- contrib/non-profit-audit-reports/csv2ods.py | 106 ++++++++++++++++++++++++++++ 1 file changed, 106 insertions(+) create mode 100755 contrib/non-profit-audit-reports/csv2ods.py (limited to 'contrib/non-profit-audit-reports/csv2ods.py') diff --git a/contrib/non-profit-audit-reports/csv2ods.py b/contrib/non-profit-audit-reports/csv2ods.py new file mode 100755 index 00000000..c0c5c6d3 --- /dev/null +++ b/contrib/non-profit-audit-reports/csv2ods.py @@ -0,0 +1,106 @@ +#!/usr/bin/python +# csv2ods.py +# Convert example csv file to ods +# +# Copyright (c) 2012 Tom Marble +# +# This program gives you software freedom; you can copy, modify, convey, +# and/or redistribute it under the terms of the GNU General Public License +# as published by the Free Software Foundation; either version 3 of the +# License, or (at your option) any later version. +# +# This program is distributed in the hope that it will be useful, but +# WITHOUT ANY WARRANTY; without even the implied warranty of +# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU +# General Public License for more details. +# +# You should have received a copy of the GNU General Public License along +# with this program in a file called 'GPLv3'. If not, write to the: +# Free Software Foundation, Inc., 51 Franklin St, Fifth Floor +# Boston, MA 02110-1301, USA. + +import sys, os, os.path, optparse +import csv +import ooolib2 + +def err(msg): + print 'error: %s' % msg + sys.exit(1) + +def csv2ods(csvname, odsname, verbose = False): + if verbose: + print 'converting from %s to %s' % (csvname, odsname) + doc = ooolib2.Calc() + # add a pagebreak style + style = 'pagebreak' + style_pagebreak = doc.styles.get_next_style('row') + style_data = tuple([style, ('style:row-height', doc.styles.property_row_height)]) + doc.styles.style_config[style_data] = style_pagebreak + # add a currency style + style = 'currency' + style_currency = doc.styles.get_next_style('cell') + style_data = tuple([style]) + doc.styles.style_config[style_data] = style_currency + + row = 1 + csvdir = os.path.dirname(csvname) + if len(csvdir) == 0: + csvdir = '.' + csvfile = open(csvname, 'rb') + reader = csv.reader(csvfile, delimiter=',', quotechar='"') + for fields in reader: + if len(fields) > 0: + for col in range(len(fields)): + val = fields[col] + if len(val) > 0 and val[0] == '$': + doc.set_cell_value(col + 1, row, 'currency', val[1:]) + else: + if ( (col == 3) and (val != 'Receipt') and len(val) > 0) or ( (col == 4) and (val != 'Invoice') and len(val) > 0): + linkrel = '../' + val # ../ means remove the name of the *.ods + linkname = os.path.basename(val) # name is just the last component + doc.set_cell_value(col + 1, row, 'link', (linkrel, linkname)) + linkpath = csvdir + '/' + val + if verbose: + if os.path.exists(linkpath): + print 'relative link %s EXISTS at %s' % (val, linkpath) + else: + print 'relative link %s DOES NOT EXIST at %s' % (val, linkpath) + else: + doc.set_cell_value(col + 1, row, 'string', val) + else: + # enter an empty string for blank lines + doc.set_cell_value(1, row, 'string', '') + # put a pagebreak here + doc.sheets[doc.sheet_index].set_sheet_config(('row', row), style_pagebreak) + row += 1 + # save the file + doc.save(odsname) + +def main(): + program = os.path.basename(sys.argv[0]) + version = '0.1' + parser = optparse.OptionParser(usage='%prog [--help] [--verbose]', + version='%prog ' + version) + parser.add_option('-v', '--verbose', action='store_true', + dest='verbose', + help='provide extra information while processing') + parser.add_option('-c', '--csv', action='store', + help='csv file to process') + parser.add_option('-o', '--ods', action='store', + help='ods output filename') + (options, args) = parser.parse_args() + if len(args) != 0: + parser.error("not expecting extra args") + if not os.path.exists(options.csv): + err('csv does not exist: %s' % options.csv) + if not options.ods: + (root, ext) = os.path.splitext(options.csv) + options.ods = root + '.ods' + if options.verbose: + print '%s: verbose mode on' % program + print 'csv:', options.csv + print 'ods:', options.ods + csv2ods(options.csv, options.ods, options.verbose) + +if __name__ == '__main__': + main() -- cgit v1.2.3 From 613eb6ba71f2c1198b7a679bde804226e27861fb Mon Sep 17 00:00:00 2001 From: "Bradley M. Kuhn" Date: Sat, 8 Sep 2012 14:34:15 -0400 Subject: Receipt/Invoice are additional information thus better if they're final columns. Change the column of Receipt and Invoice in the CSV file first, then the generated ODS file must have the same change propagated, which requires changes to the column numbers hard-coding in csv2ods.py. Perhaps if/when this application is refactored these things shouldn't be hard-coded in this way in the first place. --- contrib/non-profit-audit-reports/csv2ods.py | 3 ++- contrib/non-profit-audit-reports/general-ledger-report.plx | 4 ++-- 2 files changed, 4 insertions(+), 3 deletions(-) (limited to 'contrib/non-profit-audit-reports/csv2ods.py') diff --git a/contrib/non-profit-audit-reports/csv2ods.py b/contrib/non-profit-audit-reports/csv2ods.py index c0c5c6d3..f6150158 100755 --- a/contrib/non-profit-audit-reports/csv2ods.py +++ b/contrib/non-profit-audit-reports/csv2ods.py @@ -3,6 +3,7 @@ # Convert example csv file to ods # # Copyright (c) 2012 Tom Marble +# Copyright (c) 2012 Bradley M. Kuhn # # This program gives you software freedom; you can copy, modify, convey, # and/or redistribute it under the terms of the GNU General Public License @@ -55,7 +56,7 @@ def csv2ods(csvname, odsname, verbose = False): if len(val) > 0 and val[0] == '$': doc.set_cell_value(col + 1, row, 'currency', val[1:]) else: - if ( (col == 3) and (val != 'Receipt') and len(val) > 0) or ( (col == 4) and (val != 'Invoice') and len(val) > 0): + if ( (col == 5) and (val != 'Receipt') and len(val) > 0) or ( (col == 6) and (val != 'Invoice') and len(val) > 0): linkrel = '../' + val # ../ means remove the name of the *.ods linkname = os.path.basename(val) # name is just the last component doc.set_cell_value(col + 1, row, 'link', (linkrel, linkname)) diff --git a/contrib/non-profit-audit-reports/general-ledger-report.plx b/contrib/non-profit-audit-reports/general-ledger-report.plx index cc3dc087..5286d625 100755 --- a/contrib/non-profit-audit-reports/general-ledger-report.plx +++ b/contrib/non-profit-audit-reports/general-ledger-report.plx @@ -107,8 +107,8 @@ foreach my $acct (@sortedAccounts) { close(GL_TEXT_DATA); die "error reading ledger output for chart of accounts: $!" unless $? == 0; print GL_CSV_OUT "\n\"ACCOUNT:\",\"$acct\"\n\"PERIOD START:\",\"$beginDate\"\n\"PERIOD END:\",\"$formattedEndDate\"\n"; - print GL_CSV_OUT '"DATE","CHECK NUM","NAME","Receipt","Invoice","TRANSACTION AMT","RUNNING TOTAL"', "\n"; - @acctLedgerOpts = ('-F', '"%(date)","%C","%P","%(tag(\'Receipt\'))","%(tag(\'Invoice\'))","%t","%T"\n', '-w', '--sort', 'd', '-b', $beginDate, '-e', $endDate, @otherLedgerOpts, 'reg', $acct); + print GL_CSV_OUT '"DATE","CHECK NUM","NAME","TRANSACTION AMT","RUNNING TOTAL","Receipt","Invoice"', "\n"; + @acctLedgerOpts = ('-F', '"%(date)","%C","%P","%t","%T","%(tag(\'Receipt\'))","%(tag(\'Invoice\'))"\n', '-w', '--sort', 'd', '-b', $beginDate, '-e', $endDate, @otherLedgerOpts, 'reg', $acct); open(GL_CSV_DATA, "-|", $LEDGER_CMD, @acctLedgerOpts) or die "Unable to run $LEDGER_CMD @acctLedgerOpts: $!"; -- cgit v1.2.3 From 01dc0416b9262905e66887b29ccef31d2867b9df Mon Sep 17 00:00:00 2001 From: "Bradley M. Kuhn" Date: Wed, 21 Nov 2012 13:09:55 -0500 Subject: Support a broader set of possible tags to be placed into the spreadsheet. I've now made a hard-coded list of potential tags that are supported and will be linked to in the general ledger spreadsheet. This list should probably be in a configuration file of some sort eventually, rather than hard coded. Indeed, note that the hard-coding goes into two different scripts, and thus the lists could easily get out of sync. --- contrib/non-profit-audit-reports/csv2ods.py | 5 ++++- contrib/non-profit-audit-reports/general-ledger-report.plx | 12 ++++++++++-- 2 files changed, 14 insertions(+), 3 deletions(-) (limited to 'contrib/non-profit-audit-reports/csv2ods.py') diff --git a/contrib/non-profit-audit-reports/csv2ods.py b/contrib/non-profit-audit-reports/csv2ods.py index f6150158..59571280 100755 --- a/contrib/non-profit-audit-reports/csv2ods.py +++ b/contrib/non-profit-audit-reports/csv2ods.py @@ -24,6 +24,9 @@ import sys, os, os.path, optparse import csv import ooolib2 +file_fields = [ 'Receipt', 'Invoice', 'Statement', 'Contract', 'PurchaseOrder', + 'Approval', 'Check', 'IncomeDistributionAnalysis', 'CurrencyRate' ] + def err(msg): print 'error: %s' % msg sys.exit(1) @@ -56,7 +59,7 @@ def csv2ods(csvname, odsname, verbose = False): if len(val) > 0 and val[0] == '$': doc.set_cell_value(col + 1, row, 'currency', val[1:]) else: - if ( (col == 5) and (val != 'Receipt') and len(val) > 0) or ( (col == 6) and (val != 'Invoice') and len(val) > 0): + if ((col >= 5) and (not val in file_fields) and len(val) > 0): linkrel = '../' + val # ../ means remove the name of the *.ods linkname = os.path.basename(val) # name is just the last component doc.set_cell_value(col + 1, row, 'link', (linkrel, linkname)) diff --git a/contrib/non-profit-audit-reports/general-ledger-report.plx b/contrib/non-profit-audit-reports/general-ledger-report.plx index 5286d625..1c293db9 100755 --- a/contrib/non-profit-audit-reports/general-ledger-report.plx +++ b/contrib/non-profit-audit-reports/general-ledger-report.plx @@ -107,8 +107,16 @@ foreach my $acct (@sortedAccounts) { close(GL_TEXT_DATA); die "error reading ledger output for chart of accounts: $!" unless $? == 0; print GL_CSV_OUT "\n\"ACCOUNT:\",\"$acct\"\n\"PERIOD START:\",\"$beginDate\"\n\"PERIOD END:\",\"$formattedEndDate\"\n"; - print GL_CSV_OUT '"DATE","CHECK NUM","NAME","TRANSACTION AMT","RUNNING TOTAL","Receipt","Invoice"', "\n"; - @acctLedgerOpts = ('-F', '"%(date)","%C","%P","%t","%T","%(tag(\'Receipt\'))","%(tag(\'Invoice\'))"\n', '-w', '--sort', 'd', '-b', $beginDate, '-e', $endDate, @otherLedgerOpts, 'reg', $acct); + print GL_CSV_OUT '"DATE","CHECK NUM","NAME","TRANSACTION AMT","RUNNING TOTAL"'; + my $formatString = '"%(date)","%C","%P","%t","%T"'; + foreach my $tagField (qw/Receipt Invoice Statement Contract PurchaseOrder Approval Check IncomeDistributionAnalysis CurrencyRate/) { + print GL_CSV_OUT ',"', $tagField, '"'; + $formatString .= ',"%(tag(\'' . $tagField . '\'))"'; + } + $formatString .= "\n"; + print GL_CSV_OUT "\n"; + + @acctLedgerOpts = ('-F', $formatString, '-w', '--sort', 'd', '-b', $beginDate, '-e', $endDate, @otherLedgerOpts, 'reg', $acct); open(GL_CSV_DATA, "-|", $LEDGER_CMD, @acctLedgerOpts) or die "Unable to run $LEDGER_CMD @acctLedgerOpts: $!"; -- cgit v1.2.3 From 4f8ea18fec539c6b2e48fa7125bceaa795e899de Mon Sep 17 00:00:00 2001 From: "Bradley M. Kuhn" Date: Sun, 25 Nov 2012 13:26:01 -0500 Subject: Support selection of string encoding. Allow command line option that permits specification of string encoding, passed to Python's unicode() function. --- contrib/non-profit-audit-reports/csv2ods.py | 9 +++++++-- 1 file changed, 7 insertions(+), 2 deletions(-) (limited to 'contrib/non-profit-audit-reports/csv2ods.py') diff --git a/contrib/non-profit-audit-reports/csv2ods.py b/contrib/non-profit-audit-reports/csv2ods.py index 59571280..2b3024d4 100755 --- a/contrib/non-profit-audit-reports/csv2ods.py +++ b/contrib/non-profit-audit-reports/csv2ods.py @@ -31,7 +31,7 @@ def err(msg): print 'error: %s' % msg sys.exit(1) -def csv2ods(csvname, odsname, verbose = False): +def csv2ods(csvname, odsname, encoding='', verbose = False): if verbose: print 'converting from %s to %s' % (csvname, odsname) doc = ooolib2.Calc() @@ -56,6 +56,8 @@ def csv2ods(csvname, odsname, verbose = False): if len(fields) > 0: for col in range(len(fields)): val = fields[col] + if encoding != '': + val = unicode(val, 'utf8') if len(val) > 0 and val[0] == '$': doc.set_cell_value(col + 1, row, 'currency', val[1:]) else: @@ -92,6 +94,8 @@ def main(): help='csv file to process') parser.add_option('-o', '--ods', action='store', help='ods output filename') + parser.add_option('-e', '--encoding', action='store', + help='unicode character encoding type') (options, args) = parser.parse_args() if len(args) != 0: parser.error("not expecting extra args") @@ -104,7 +108,8 @@ def main(): print '%s: verbose mode on' % program print 'csv:', options.csv print 'ods:', options.ods - csv2ods(options.csv, options.ods, options.verbose) + print 'ods:', options.encoding + csv2ods(options.csv, options.ods, options.verbose, options.encoding) if __name__ == '__main__': main() -- cgit v1.2.3 From fbd6c309031393950ac387b9de5f07214c5fa3cf Mon Sep 17 00:00:00 2001 From: "Bradley M. Kuhn" Date: Thu, 3 Jan 2013 13:36:38 -0500 Subject: Add option to skip generating the page breaks. --- contrib/non-profit-audit-reports/csv2ods.py | 14 +++++++++----- 1 file changed, 9 insertions(+), 5 deletions(-) (limited to 'contrib/non-profit-audit-reports/csv2ods.py') diff --git a/contrib/non-profit-audit-reports/csv2ods.py b/contrib/non-profit-audit-reports/csv2ods.py index 2b3024d4..aded8e65 100755 --- a/contrib/non-profit-audit-reports/csv2ods.py +++ b/contrib/non-profit-audit-reports/csv2ods.py @@ -2,8 +2,8 @@ # csv2ods.py # Convert example csv file to ods # -# Copyright (c) 2012 Tom Marble -# Copyright (c) 2012 Bradley M. Kuhn +# Copyright (c) 2012 Tom Marble +# Copyright (c) 2012, 2013 Bradley M. Kuhn # # This program gives you software freedom; you can copy, modify, convey, # and/or redistribute it under the terms of the GNU General Public License @@ -31,7 +31,7 @@ def err(msg): print 'error: %s' % msg sys.exit(1) -def csv2ods(csvname, odsname, encoding='', verbose = False): +def csv2ods(csvname, odsname, encoding='', verbose = False, skip_page_break = False): if verbose: print 'converting from %s to %s' % (csvname, odsname) doc = ooolib2.Calc() @@ -77,7 +77,8 @@ def csv2ods(csvname, odsname, encoding='', verbose = False): # enter an empty string for blank lines doc.set_cell_value(1, row, 'string', '') # put a pagebreak here - doc.sheets[doc.sheet_index].set_sheet_config(('row', row), style_pagebreak) + if not skip_page_break: + doc.sheets[doc.sheet_index].set_sheet_config(('row', row), style_pagebreak) row += 1 # save the file doc.save(odsname) @@ -96,6 +97,9 @@ def main(): help='ods output filename') parser.add_option('-e', '--encoding', action='store', help='unicode character encoding type') + parser.add_option('-s', '--skip-page-break', action='store_true', + dest='skip_page_break', + help='do not add any page breaks') (options, args) = parser.parse_args() if len(args) != 0: parser.error("not expecting extra args") @@ -109,7 +113,7 @@ def main(): print 'csv:', options.csv print 'ods:', options.ods print 'ods:', options.encoding - csv2ods(options.csv, options.ods, options.verbose, options.encoding) + csv2ods(options.csv, options.ods, options.verbose, options.encoding, options.skip_page_break) if __name__ == '__main__': main() -- cgit v1.2.3 From 39db5bbce77c3173b006755fc71ce95ba0e4041a Mon Sep 17 00:00:00 2001 From: "Bradley M. Kuhn" Date: Sun, 6 Jan 2013 08:21:35 -0500 Subject: Ordering of options had always been incorrect on this call; Fixed. --- contrib/non-profit-audit-reports/csv2ods.py | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) (limited to 'contrib/non-profit-audit-reports/csv2ods.py') diff --git a/contrib/non-profit-audit-reports/csv2ods.py b/contrib/non-profit-audit-reports/csv2ods.py index aded8e65..e6d33906 100755 --- a/contrib/non-profit-audit-reports/csv2ods.py +++ b/contrib/non-profit-audit-reports/csv2ods.py @@ -113,7 +113,7 @@ def main(): print 'csv:', options.csv print 'ods:', options.ods print 'ods:', options.encoding - csv2ods(options.csv, options.ods, options.verbose, options.encoding, options.skip_page_break) + csv2ods(options.csv, options.ods, options.encoding, options.verbose, options.skip_page_break) if __name__ == '__main__': main() -- cgit v1.2.3 From 8cddda4c3eb67234c12285fe52bd2bc328f6678b Mon Sep 17 00:00:00 2001 From: "Bradley M. Kuhn" Date: Sun, 6 Jan 2013 19:43:54 -0500 Subject: More flexible CSV -> ODS hyperlinks and pagebreaks; csv2ods.py produces MANIFEST. Previous version of csv2ods.py simply assumed that fields beyond five would have links to files. This obviously lacked flexibility and was a silly hard-code. Now, those CSV fields that have link:SOMETHING will cause a hyperlink to be created to SOMETHING. Meanwhile, the pagebreak support was similarly hard-coded. Now, any CSV field that has the word "pagebreak" in it will generate a pagebreak. The general ledger and cash receipts/disbursement journals have been modified to make use of these new features in csv2ods.py. Finally, the --skip-page-break option is now moot in csv2ods.py, so that is herein removed. --- .../cash-receipts-and-disbursments-journals.plx | 4 +- contrib/non-profit-audit-reports/csv2ods.py | 43 +++++++++++++--------- .../general-ledger-report.plx | 6 ++- 3 files changed, 32 insertions(+), 21 deletions(-) (limited to 'contrib/non-profit-audit-reports/csv2ods.py') diff --git a/contrib/non-profit-audit-reports/cash-receipts-and-disbursments-journals.plx b/contrib/non-profit-audit-reports/cash-receipts-and-disbursments-journals.plx index 346e4064..2ad18a44 100755 --- a/contrib/non-profit-audit-reports/cash-receipts-and-disbursments-journals.plx +++ b/contrib/non-profit-audit-reports/cash-receipts-and-disbursments-journals.plx @@ -113,7 +113,7 @@ foreach my $acct (@accounts) { my $formatString = '\n"%(date)","%C","%P","%A","%t"\n%/"","","","%A","%t"'; foreach my $tagField (qw/Receipt Invoice Statement Contract PurchaseOrder Approval Check IncomeDistributionAnalysis CurrencyRate/) { print CSV_OUT ',"', $tagField, '"'; - $formatString .= ',"%(tag(\'' . $tagField . '\'))"'; + $formatString .= ',"link:%(tag(\'' . $tagField . '\'))"'; } $formatString .= "\n"; print CSV_OUT "\n"; @@ -130,7 +130,7 @@ foreach my $acct (@accounts) { open(CSV_DATA, "-|", $LEDGER_CMD, @csvRegLedgerOpts) or die "unable to run ledger command for $fileNameBase.csv: $!"; - while (my $line = ) { print CSV_OUT $line; } + while (my $line = ) { $line =~ s/"link:"/""/g; print CSV_OUT $line; } close(CSV_DATA); die "Error read from csv ledger command $!" unless $? == 0; SKIP_REGISTER_COMMANDS: diff --git a/contrib/non-profit-audit-reports/csv2ods.py b/contrib/non-profit-audit-reports/csv2ods.py index e6d33906..8b880648 100755 --- a/contrib/non-profit-audit-reports/csv2ods.py +++ b/contrib/non-profit-audit-reports/csv2ods.py @@ -24,14 +24,13 @@ import sys, os, os.path, optparse import csv import ooolib2 -file_fields = [ 'Receipt', 'Invoice', 'Statement', 'Contract', 'PurchaseOrder', - 'Approval', 'Check', 'IncomeDistributionAnalysis', 'CurrencyRate' ] - def err(msg): print 'error: %s' % msg sys.exit(1) -def csv2ods(csvname, odsname, encoding='', verbose = False, skip_page_break = False): +def csv2ods(csvname, odsname, encoding='', verbose = False): + filesSavedinManifest = {} + if verbose: print 'converting from %s to %s' % (csvname, odsname) doc = ooolib2.Calc() @@ -45,7 +44,7 @@ def csv2ods(csvname, odsname, encoding='', verbose = False, skip_page_break = Fa style_currency = doc.styles.get_next_style('cell') style_data = tuple([style]) doc.styles.style_config[style_data] = style_currency - + row = 1 csvdir = os.path.dirname(csvname) if len(csvdir) == 0: @@ -61,26 +60,39 @@ def csv2ods(csvname, odsname, encoding='', verbose = False, skip_page_break = Fa if len(val) > 0 and val[0] == '$': doc.set_cell_value(col + 1, row, 'currency', val[1:]) else: - if ((col >= 5) and (not val in file_fields) and len(val) > 0): + if (len(val) > 0 and val[0:5] == "link:"): + val = val[5:] linkrel = '../' + val # ../ means remove the name of the *.ods linkname = os.path.basename(val) # name is just the last component doc.set_cell_value(col + 1, row, 'link', (linkrel, linkname)) linkpath = csvdir + '/' + val + + if not val in filesSavedinManifest: + filesSavedinManifest[val] = col + + if not os.path.exists(linkpath): + print "WARNING: link %s DOES NOT EXIST at %s" % (val, linkpath) if verbose: if os.path.exists(linkpath): print 'relative link %s EXISTS at %s' % (val, linkpath) - else: - print 'relative link %s DOES NOT EXIST at %s' % (val, linkpath) else: - doc.set_cell_value(col + 1, row, 'string', val) + if val == "pagebreak": + doc.sheets[doc.sheet_index].set_sheet_config(('row', row), style_pagebreak) + else: + doc.set_cell_value(col + 1, row, 'string', val) else: # enter an empty string for blank lines doc.set_cell_value(1, row, 'string', '') - # put a pagebreak here - if not skip_page_break: - doc.sheets[doc.sheet_index].set_sheet_config(('row', row), style_pagebreak) row += 1 - # save the file + # save manifest file + if filesSavedinManifest.keys() != []: + manifestFH = open("MANIFEST", "a") + manifestFH.write("# Files from %s\n" % odsname) + for file in filesSavedinManifest.keys(): + manifestFH.write("%s\n" % file) + + manifestFH.close() + # Save spreadsheet file. doc.save(odsname) def main(): @@ -97,9 +109,6 @@ def main(): help='ods output filename') parser.add_option('-e', '--encoding', action='store', help='unicode character encoding type') - parser.add_option('-s', '--skip-page-break', action='store_true', - dest='skip_page_break', - help='do not add any page breaks') (options, args) = parser.parse_args() if len(args) != 0: parser.error("not expecting extra args") @@ -113,7 +122,7 @@ def main(): print 'csv:', options.csv print 'ods:', options.ods print 'ods:', options.encoding - csv2ods(options.csv, options.ods, options.encoding, options.verbose, options.skip_page_break) + csv2ods(options.csv, options.ods, options.encoding, options.verbose) if __name__ == '__main__': main() diff --git a/contrib/non-profit-audit-reports/general-ledger-report.plx b/contrib/non-profit-audit-reports/general-ledger-report.plx index 3b230837..1fd0e7ce 100755 --- a/contrib/non-profit-audit-reports/general-ledger-report.plx +++ b/contrib/non-profit-audit-reports/general-ledger-report.plx @@ -181,7 +181,7 @@ foreach my $acct (@sortedAccounts) { my $formatString = '"%(date)","%C","%P","%t",""'; foreach my $tagField (qw/Receipt Invoice Statement Contract PurchaseOrder Approval Check IncomeDistributionAnalysis CurrencyRate/) { print GL_CSV_OUT ',"', $tagField, '"'; - $formatString .= ',"%(tag(\'' . $tagField . '\'))"'; + $formatString .= ',"link:%(tag(\'' . $tagField . '\'))"'; } $formatString .= "\n"; print GL_CSV_OUT "\n"; @@ -195,12 +195,14 @@ foreach my $acct (@sortedAccounts) { or die "Unable to run $LEDGER_CMD @acctLedgerOpts: $!"; foreach my $line () { + $line =~ s/"link:"/""/g; print GL_CSV_OUT $line; next if $line =~ /ACCOUNT:.*PERIOD/; # Skip column header lines $line =~ s/^"[^"]*","[^"]*","[^"]*","[^"]*","[^"]*",//; while ($line =~ s/^"([^"]*)"(,|$)//) { my $file = $1; next if $file =~ /^\s*$/; + $file =~ s/^link:(.*)$/$1/; warn "$file does not exist and/or is not readable" unless -r $file; print MANIFEST "$file\n" if not defined $manifest{$file}; $manifest{$file} = $line; @@ -210,7 +212,7 @@ foreach my $acct (@sortedAccounts) { $balanceData{totalEnd}{$acct} = $ZERO unless defined $balanceData{totalEnd}{$acct}; print GL_CSV_OUT "\"$formattedEndDate\"", ',"","BALANCE","","$', "$balanceData{totalEnd}{$acct}\"\n"; } - + print GL_CSV_OUT "pagebreak\n"; close(GL_CSV_DATA); die "error reading ledger output for chart of accounts: $!" unless $? == 0; } close(GL_TEXT_OUT); die "error writing to general-ledger.txt: $!" unless $? == 0; -- cgit v1.2.3 From 34a6279baa3c836c1850f335c1c7ec3089fb2532 Mon Sep 17 00:00:00 2001 From: "Bradley M. Kuhn" Date: Mon, 7 Jan 2013 16:38:53 -0500 Subject: Add --single-file-directory option: create single directory with all linked files. This new option copies all files to the directory specified as an argument to the --single-file-directory option, and also creates dummy shorter filenames for the files. This feature was implemented to get around a problem found when zip'ing the spreadsheet up with the supporting files for users on Windows. The Windows users encounter the error 0x80010135 related to some of the ZIP files going beyond the maximum path name length on windows. Apparently, opening ZIP files with long path names just doesn't work on Microsoft systems. I've suggested our accountants switch to a Free Software operating system, but they declined. --- contrib/non-profit-audit-reports/csv2ods.py | 51 ++++++++++++++++++++++++++--- 1 file changed, 46 insertions(+), 5 deletions(-) (limited to 'contrib/non-profit-audit-reports/csv2ods.py') diff --git a/contrib/non-profit-audit-reports/csv2ods.py b/contrib/non-profit-audit-reports/csv2ods.py index 8b880648..3a3411ba 100755 --- a/contrib/non-profit-audit-reports/csv2ods.py +++ b/contrib/non-profit-audit-reports/csv2ods.py @@ -23,16 +23,23 @@ import sys, os, os.path, optparse import csv import ooolib2 +import shutil +import string def err(msg): print 'error: %s' % msg sys.exit(1) -def csv2ods(csvname, odsname, encoding='', verbose = False): +def csv2ods(csvname, odsname, encoding='', singleFileDirectory=None, verbose = False): filesSavedinManifest = {} if verbose: print 'converting from %s to %s' % (csvname, odsname) + + if singleFileDirectory: + if not os.path.isdir(os.path.join(os.getcwd(),singleFileDirectory)): + os.mkdir(singleFileDirectory) + doc = ooolib2.Calc() # add a pagebreak style style = 'pagebreak' @@ -55,20 +62,51 @@ def csv2ods(csvname, odsname, encoding='', verbose = False): if len(fields) > 0: for col in range(len(fields)): val = fields[col] - if encoding != '': + if encoding != '' and val[0:5] != "link:": # Only utf8 encode if it's not a filename val = unicode(val, 'utf8') if len(val) > 0 and val[0] == '$': doc.set_cell_value(col + 1, row, 'currency', val[1:]) else: if (len(val) > 0 and val[0:5] == "link:"): val = val[5:] - linkrel = '../' + val # ../ means remove the name of the *.ods linkname = os.path.basename(val) # name is just the last component + if not singleFileDirectory: + newFile = val + else: + relativeFileWithPath = os.path.basename(val) + fileName, fileExtension = os.path.splitext(relativeFileWithPath) + newFile = fileName[:15] # 15 is an arbitrary choice. + newFile = newFile + fileExtension + # We'll now test to see if we made this file + # before, and if it matched the same file we + # now want. If it doesn't, try to make a + # short file name for it. + if filesSavedinManifest.has_key(newFile) and filesSavedinManifest[newFile] != val: + testFile = None + for cc in list(string.letters) + list(string.digits): + testFile = cc + newFile + if not filesSavedinManifest.has_key(testFile): + break + testFile = None + if not testFile: + raise Exception("too many similar file names for linkage; giving up") + else: + newFile = testFile + if not os.path.exists(csvdir + '/' + val): + raise Exception("File" + csvdir + '/' + val + " does not exist in single file directory mode; giving up") + src = os.path.join(csvdir, val) + dest = os.path.join(csvdir, singleFileDirectory, newFile) + shutil.copyfile(src, dest) + shutil.copystat(src, dest) + shutil.copymode(src, dest) + newFile = os.path.join(singleFileDirectory, newFile) + + linkrel = '../' + newFile # ../ means remove the name of the *.ods doc.set_cell_value(col + 1, row, 'link', (linkrel, linkname)) linkpath = csvdir + '/' + val if not val in filesSavedinManifest: - filesSavedinManifest[val] = col + filesSavedinManifest[newFile] = val if not os.path.exists(linkpath): print "WARNING: link %s DOES NOT EXIST at %s" % (val, linkpath) @@ -109,7 +147,10 @@ def main(): help='ods output filename') parser.add_option('-e', '--encoding', action='store', help='unicode character encoding type') + parser.add_option('-d', '--single-file-directory', action='store', + help='directory name to move all files into') (options, args) = parser.parse_args() + if len(args) != 0: parser.error("not expecting extra args") if not os.path.exists(options.csv): @@ -122,7 +163,7 @@ def main(): print 'csv:', options.csv print 'ods:', options.ods print 'ods:', options.encoding - csv2ods(options.csv, options.ods, options.encoding, options.verbose) + csv2ods(options.csv, options.ods, options.encoding, options.single_file_directory, options.verbose) if __name__ == '__main__': main() -- cgit v1.2.3 From d13ab6a4026cfeec18fdd989862aecbe83caa20f Mon Sep 17 00:00:00 2001 From: "Bradley M. Kuhn" Date: Wed, 9 Jan 2013 14:51:33 -0500 Subject: Support for a list of known checksums of files already copied over. For the times when we want to make shorter names of files by doing copies of the documentation files for hyperlink usage, allow input of a new command line option which is a list in the form of: PATH_TO_FILE : sha25sum so that those files can be used rather than new copies made. --- contrib/non-profit-audit-reports/csv2ods.py | 79 +++++++++++++++++++++++++---- 1 file changed, 70 insertions(+), 9 deletions(-) (limited to 'contrib/non-profit-audit-reports/csv2ods.py') diff --git a/contrib/non-profit-audit-reports/csv2ods.py b/contrib/non-profit-audit-reports/csv2ods.py index 3a3411ba..7dd840c8 100755 --- a/contrib/non-profit-audit-reports/csv2ods.py +++ b/contrib/non-profit-audit-reports/csv2ods.py @@ -25,14 +25,46 @@ import csv import ooolib2 import shutil import string +from Crypto.Hash import SHA256 def err(msg): print 'error: %s' % msg sys.exit(1) -def csv2ods(csvname, odsname, encoding='', singleFileDirectory=None, verbose = False): +def ReadChecksums(inputFile): + checksums = {} + with open(inputFile, "r") as inputFH: + entries = inputFH.readlines() + for ee in entries: + fileName, checksum = ee.split(":") + fileName = fileName.replace(' ', "") + checksum = checksum.replace(' ', "") + checksum = checksum.replace("\n", "") + checksums[checksum] = fileName + return checksums + +def ChecksumFile(filename): + sha256 = SHA256.new() + chunk_size = 8192 + with open(filename, 'rb') as myFile: + while True: + chunk = myFile.read(chunk_size) + if len(chunk) == 0: + break + sha256.update(chunk) + return sha256.hexdigest() + +def main(): + program = os.path.basename(sys.argv[0]) + + print get_file_checksum(sys.argv[1]) + +def csv2ods(csvname, odsname, encoding='', singleFileDirectory=None, knownChecksums={}, verbose = False): filesSavedinManifest = {} + if knownChecksums: + checksumCache = {} + if verbose: print 'converting from %s to %s' % (csvname, odsname) @@ -70,10 +102,25 @@ def csv2ods(csvname, odsname, encoding='', singleFileDirectory=None, verbose = F if (len(val) > 0 and val[0:5] == "link:"): val = val[5:] linkname = os.path.basename(val) # name is just the last component + newFile = None + if not singleFileDirectory: newFile = val - else: + + if knownChecksums: + if not checksumCache.has_key(val): + checksum = ChecksumFile(val) + checksumCache[val] = checksum + else: + checksum = checksumCache[val] + + if knownChecksums.has_key(checksum): + newFile = knownChecksums[checksum] + print "FOUND new file in known: " + newFile + + if not newFile: relativeFileWithPath = os.path.basename(val) + fileName, fileExtension = os.path.splitext(relativeFileWithPath) newFile = fileName[:15] # 15 is an arbitrary choice. newFile = newFile + fileExtension @@ -88,19 +135,24 @@ def csv2ods(csvname, odsname, encoding='', singleFileDirectory=None, verbose = F if not filesSavedinManifest.has_key(testFile): break testFile = None - if not testFile: - raise Exception("too many similar file names for linkage; giving up") - else: - newFile = testFile - if not os.path.exists(csvdir + '/' + val): - raise Exception("File" + csvdir + '/' + val + " does not exist in single file directory mode; giving up") + if not testFile: + raise Exception("too many similar file names for linkage; giving up") + else: + newFile = testFile + if not os.path.exists(csvdir + '/' + val): + raise Exception("File" + csvdir + '/' + val + " does not exist in single file directory mode; giving up") src = os.path.join(csvdir, val) dest = os.path.join(csvdir, singleFileDirectory, newFile) shutil.copyfile(src, dest) shutil.copystat(src, dest) shutil.copymode(src, dest) + newFile = os.path.join(singleFileDirectory, newFile) + if knownChecksums: + checksumCache[checksum] = newFile + knownChecksums[checksum] = newFile + linkrel = '../' + newFile # ../ means remove the name of the *.ods doc.set_cell_value(col + 1, row, 'link', (linkrel, linkname)) linkpath = csvdir + '/' + val @@ -149,6 +201,8 @@ def main(): help='unicode character encoding type') parser.add_option('-d', '--single-file-directory', action='store', help='directory name to move all files into') + parser.add_option('-s', '--known-checksum-list', action='store', + help='directory name to move all files into') (options, args) = parser.parse_args() if len(args) != 0: @@ -163,7 +217,14 @@ def main(): print 'csv:', options.csv print 'ods:', options.ods print 'ods:', options.encoding - csv2ods(options.csv, options.ods, options.encoding, options.single_file_directory, options.verbose) + if options.known_checksum_list and not options.single_file_directory: + err(program + ": --known-checksum-list option is completely useless without --single-file-directory") + knownChecksums = {} + if options.known_checksum_list: + if not os.access(options.known_checksum_list, os.R_OK): + err(program + ": unable to read file: " + options.known_checksum_list) + knownChecksums = ReadChecksums(options.known_checksum_list) + csv2ods(options.csv, options.ods, options.encoding, options.single_file_directory, knownChecksums, options.verbose) if __name__ == '__main__': main() -- cgit v1.2.3 From 9fcdfc893c41447099169bf4ad51f5856db399a1 Mon Sep 17 00:00:00 2001 From: "Bradley M. Kuhn" Date: Thu, 10 Jan 2013 11:26:14 -0500 Subject: Support for title: field, to name sheets in the ODS file. If "title:SOMETHING" occurs in the CSV file, use SOMETHING as the title of the sheet. --- contrib/non-profit-audit-reports/csv2ods.py | 5 ++++- 1 file changed, 4 insertions(+), 1 deletion(-) (limited to 'contrib/non-profit-audit-reports/csv2ods.py') diff --git a/contrib/non-profit-audit-reports/csv2ods.py b/contrib/non-profit-audit-reports/csv2ods.py index 7dd840c8..6aabcb59 100755 --- a/contrib/non-profit-audit-reports/csv2ods.py +++ b/contrib/non-profit-audit-reports/csv2ods.py @@ -169,7 +169,10 @@ def csv2ods(csvname, odsname, encoding='', singleFileDirectory=None, knownChecks if val == "pagebreak": doc.sheets[doc.sheet_index].set_sheet_config(('row', row), style_pagebreak) else: - doc.set_cell_value(col + 1, row, 'string', val) + if val[0:6] == "title:": + doc.sheets[doc.sheet_index].set_name(val[6:]) + else: + doc.set_cell_value(col + 1, row, 'string', val) else: # enter an empty string for blank lines doc.set_cell_value(1, row, 'string', '') -- cgit v1.2.3