def write_row(self,
tran_type,
nominal,
reference,
date,
details,
net_amount,
tax_code,
account='',
tax_amount=0.0,
exchange_rate=1,
extra_ref='',
user_name='PySage50',
comment=''):
if p(net_amount) == p(0) and p(tax_amount) == p(0):
pass # don't write out anything for zero value as not needed
else:
if user_name == '':
user_name = self.user
# Don't be tempted to put spaces after comma's. SAGE LINE 50 WILL REJECT IT.
self.f.write(tran_type + ',')
self.f.write(account + ',')
self.f.write(nominal + ',')
self.f.write(dt.datetime.strftime(date, '%d/%m/%Y') + ',')
self.f.write(reference + ',')
self.f.write(details + ',')
self.f.write('{0:.2f}'.format(net_amount) + ',')
self.f.write(tax_code + ',')
self.f.write('{0:.2f}'.format(tax_amount) + ',') # Tax amount
self.f.write('{0:.2f}'.format(exchange_rate) + ',')
self.f.write(extra_ref + ',')
self.f.write(user_name)
if comment != '':
self.f.write(',' + comment)
self.f.write('\n')
def enrich_remittance_doc(self, remittance_doc):
"""Enrich a raw remittance document with data from Sage
It uses getField which uses 3 predefined columns:
'Your Ref' is our invoice number
'Member Code' is an AIS specfic membership code and defines some exceptions
'Document Type' defines the type of document. We are only enriching 'Invoice' and 'Credit Note'
"""
def get_series(field):
return remittance_doc.df.apply(lambda row: self.get_field(row, field), axis=1)
remittance_doc.df['Account_Ref'] = get_series('ALT_REF')
remittance_doc.df['Sage_Net_Amount'] = get_series('NET_AMOUNT')
remittance_doc.df['Sage_Gross_Amount'] = get_series('GROSS_AMOUNT')
remittance_doc.df['Sage_VAT_Amount'] = get_series('TAX_AMOUNT')
remittance_doc.df['Sage_Tax_Rate'] = get_series('TAX_RATE') / 100
net = remittance_doc.df['Sage_Net_Amount'].sum()
vat = remittance_doc.df['Sage_VAT_Amount'].sum()
gross = remittance_doc.df['Sage_Gross_Amount'].sum()
# Check sage calculations - shouldn't be a problem. if this is passed can then rely on two of the
# three values to set the third. Note due to rounding you can't calculate them except approximately unless
# you have access to the line items.
if ( p(net + vat) != p(gross) ):
remittance_doc.checked = False
raise PySageError("Internal calcs of sum in Sage don't add up. net + vat != gross, {} + {} != {}".format(
net, vat, gross
))
# Check that gross AIS doc values match Sage gross values TODO remove specific for local installation
gross_sum_ex_discount = remittance_doc.df[remittance_doc.df['Member Code'] != '4552']['Sage_Gross_Amount'].sum()
if gross != gross_sum_ex_discount:
remittance_doc.checked = False
raise PySageError("Adding up total AIS invoices doesn't equal Sage sum, {} != {}, types {}, {}".format(
gross_sum_ex_discount, gross, type(gross_sum_ex_discount), type(gross)
))
def test_SagePurchaseInvoice(self):
spi = SagePurchaseInvoice()
self.assertEqual(spi.net_amount, p(0), 'Default is zero')
spi.net_amount = 42
self.assertEqual(spi.net_amount, p(42), 'By default assign to both')
spi.payment = None
self.assertEqual(spi.net_amount, p(42), 'If no payment then invoice should still work')
spi.net_amount = 43
self.assertEqual(spi.net_amount, p(43), 'Can assign to invoice even when no payment')
def test_SagePurchaseInvoice(self):
spi = SagePurchaseInvoice()
self.assertEqual(spi.net_amount, p(0), 'Default is zero')
spi.net_amount = 42
self.assertEqual(spi.net_amount, p(42), 'By default assign to both')
spi.payment = None
self.assertEqual(spi.net_amount, p(42),
'If no payment then invoice should still work')
spi.net_amount = 43
self.assertEqual(spi.net_amount, p(43),
'Can assign to invoice even when no payment')
def using_reference_get(self, i, field, numchars=30, record_type=['SI']):
"""
Using the invoice number we can look up the field. The accounting database contains line entries.
So this aggregates the line entries and returns the sum of the field if numeric.
"""
df = self.sqldata[(self.sqldata['TYPE'].isin(record_type))
& (self.sqldata['ACCOUNT_REF'] == '1100')
& (self.sqldata['INV_REF'] == str(i))]
if len(df) == 0: # It is an error to look up data where there is none
raise PySageError(
'No data found in Audit Header to match invoice {}'.format(i))
elif field in ['TRAN_NUMBER']:
return list(df[:1][field])[0]
elif field in [
'DATE', 'TYPE', 'ACCOUNT_REF', 'ALT_REF', 'INV_REF',
'TAX_CODE', 'BANK_FLAG', 'DATE_BANK_RECONCILED'
]:
return list(df[field])[0]
elif field in ['OUTSTANDING']:
return p(list(df[field])[0])
elif field in ['AMOUNT', 'FOREIGN_AMOUNT']:
return p(df[field].sum())
elif field == 'GROSS_AMOUNT':
return p(df['AMOUNT'].sum())
elif field in ['NET_AMOUNT']:
df2 = self.sqldata[(self.sqldata['TYPE'].isin(record_type))
& (self.sqldata['ACCOUNT_REF'] == '2200'
) # Get VAT control account
& (self.sqldata['INV_REF'] == str(i))]
return p(df['AMOUNT'].sum() + df2['AMOUNT'].sum())
elif field in ['TAX_AMOUNT']:
df2 = self.sqldata[(self.sqldata['TYPE'].isin(record_type))
& (self.sqldata['ACCOUNT_REF'] == '2200'
) # Get VAT control account
& (self.sqldata['INV_REF'] == str(i))]
return p(-df2['AMOUNT'].sum())
elif field in ['TAX_RATE']:
df2 = self.sqldata[(self.sqldata['TYPE'].isin(record_type))
& (self.sqldata['ACCOUNT_REF'] == '4000'
) # Get net Sales amount
& (self.sqldata['INV_REF'] == str(i))]
return 100 * (
(float(df['AMOUNT'].sum()) / float(-df2['AMOUNT'].sum())) -
1.0)
elif field in ['DETAILS', 'EXTRA_REF']:
return df[field].str.cat()[:numchars]
else:
raise PySageError(
'Unmatched get field {} for using_invoice_get '.format(field))
def detailed_check_for_transactions_in_the_month(self, journal_type,
account, date, details):
en = date + pd.offsets.MonthEnd(0)
st = en - pd.offsets.MonthBegin(1)
test1 = self.sqldata[self.sqldata['ACCOUNT_REF'] == int(account)]
test2 = test1[test1['DATE'] >= st]
test3 = test2[test2['DATE'] <= en]
test = test3[
test3['DETAILS'] ==
details] # Exact match is ok since looking for machine duplicates
l = len(test)
if l == 0:
comment = 'Found no transactions from {} upto {} .'.format(
st.strftime('%Y-%m-%d'),
en.strftime('%Y-%m-%d'),
)
return (False, 0, comment)
else:
tn = test[:1]
comment = 'Found {} transactions from {} upto {}. First was on {}: details {}: for {}.'.format(
l,
st.strftime('%Y-%m-%d'),
en.strftime('%Y-%m-%d'),
list(tn['DATE'])[0].strftime('%Y-%m-%d'),
list(tn['DETAILS'])[0],
p(list(tn['AMOUNT'])[0]),
)
return (True, 0, comment)
def check_for_transactions_in_the_month(self, journal_type, account, date):
# c = 'Type of date {} account = {} Type of account {} journal type = {}'.format(type(date), account,
# type(account), journal_type)
# return (True, 0, c)
# d2 = pd.to_datetime(date, format='%d/%m/%Y')
# d2 = dt.datetime(2014,12,15)
en = date + pd.offsets.MonthEnd(0)
st = en - pd.offsets.MonthBegin(1)
test2 = self.sqldata[self.sqldata['ACCOUNT_REF'] == int(account)]
test1 = test2[test2['DATE'] >= st]
test = test1[test1['DATE'] <= en]
l = len(test)
if l == 0:
comment = 'Found no transactions from {} upto {}.'.format(
st.strftime('%Y-%m-%d'),
en.strftime('%Y-%m-%d'),
)
return (False, 0, comment)
else:
tn = test[:1]
# TODO make next a function and reuse below
comment = 'Found {} transactions from {} upto {}. First was on {}: details {}: for {}.'.format(
l,
st.strftime('%Y-%m-%d'),
en.strftime('%Y-%m-%d'),
list(tn['DATE'])[0].strftime('%Y-%m-%d'),
list(tn['DETAILS'])[0],
p(list(tn['AMOUNT'])[0]),
)
return (True, 0, comment)
def check_for_transactions_in_the_month(self, journal_type, account, date):
# c = 'Type of date {} account = {} Type of account {} journal type = {}'.format(type(date), account,
# type(account), journal_type)
# return (True, 0, c)
# d2 = pd.to_datetime(date, format='%d/%m/%Y')
# d2 = dt.datetime(2014,12,15)
en = date + pd.offsets.MonthEnd(0)
st = en - pd.offsets.MonthBegin(1)
test2 = self.sqldata[self.sqldata['ACCOUNT_REF'] == int(account)]
test1 = test2[test2['DATE'] >= st]
test = test1[test1['DATE'] <= en]
l = len(test)
if l == 0:
comment = 'Found no transactions from {} upto {}.'.format(
st.strftime('%Y-%m-%d'), en.strftime('%Y-%m-%d'), )
return (False, 0, comment)
else:
tn = test[:1]
# TODO make next a function and reuse below
comment = 'Found {} transactions from {} upto {}. First was on {}: details {}: for {}.'.format(
l, st.strftime('%Y-%m-%d'), en.strftime('%Y-%m-%d'),
list(tn['DATE'])[0].strftime('%Y-%m-%d'),
list(tn['DETAILS'])[0],
p(list(tn['AMOUNT'])[0]),)
return (True, 0, comment)
def using_reference_get(self, i, field, numchars=30, record_type = ['SI']):
"""
Using the invoice number we can look up the field. The accounting database contains line entries.
So this aggregates the line entries and returns the sum of the field if numeric.
"""
df = self.sqldata[(self.sqldata['TYPE'].isin(record_type))
& (self.sqldata['ACCOUNT_REF'] == '1100')
& (self.sqldata['INV_REF'] == str(i))
]
if len(df) == 0: # It is an error to look up data where there is none
raise PySageError('No data found in Audit Header to match invoice {}'.format(i))
elif field in ['TRAN_NUMBER']:
return list(df[:1][field])[0]
elif field in ['DATE', 'TYPE', 'ACCOUNT_REF', 'ALT_REF', 'INV_REF', 'TAX_CODE',
'BANK_FLAG', 'DATE_BANK_RECONCILED']:
return list(df[field])[0]
elif field in ['OUTSTANDING']:
return p(list(df[field])[0])
elif field in ['AMOUNT', 'FOREIGN_AMOUNT']:
return p(df[field].sum())
elif field == 'GROSS_AMOUNT':
return p(df['AMOUNT'].sum())
elif field in ['NET_AMOUNT']:
df2 = self.sqldata[(self.sqldata['TYPE'].isin(record_type))
& (self.sqldata['ACCOUNT_REF'] == '2200') # Get VAT control account
& (self.sqldata['INV_REF']== str(i))
]
return p(df['AMOUNT'].sum() + df2['AMOUNT'].sum())
elif field in ['TAX_AMOUNT']:
df2 = self.sqldata[(self.sqldata['TYPE'].isin(record_type))
& (self.sqldata['ACCOUNT_REF'] == '2200') # Get VAT control account
& (self.sqldata['INV_REF']== str(i))
]
return p(- df2['AMOUNT'].sum())
elif field in ['TAX_RATE']:
df2 = self.sqldata[(self.sqldata['TYPE'].isin(record_type))
& (self.sqldata['ACCOUNT_REF'] == '4000') # Get net Sales amount
& (self.sqldata['INV_REF']== str(i))
]
return 100 * ((float(df['AMOUNT'].sum()) / float(- df2['AMOUNT'].sum())) - 1.0)
elif field in ['DETAILS', 'EXTRA_REF']:
return df[field].str.cat()[:numchars]
else:
raise PySageError('Unmatched get field {} for using_invoice_get '.format(field))
def enrich_remittance_doc(self, remittance_doc):
"""Enrich a raw remittance document with data from Sage
It uses getField which uses 3 predefined columns:
'Your Ref' is our invoice number
'Member Code' is an AIS specfic membership code and defines some exceptions
'Document Type' defines the type of document. We are only enriching 'Invoice' and 'Credit Note'
"""
def get_series(field):
return remittance_doc.df.apply(
lambda row: self.get_field(row, field), axis=1)
remittance_doc.df['Account_Ref'] = get_series('ALT_REF')
remittance_doc.df['Sage_Net_Amount'] = get_series('NET_AMOUNT')
remittance_doc.df['Sage_Gross_Amount'] = get_series('GROSS_AMOUNT')
remittance_doc.df['Sage_VAT_Amount'] = get_series('TAX_AMOUNT')
remittance_doc.df['Sage_Tax_Rate'] = get_series('TAX_RATE') / 100
net = remittance_doc.df['Sage_Net_Amount'].sum()
vat = remittance_doc.df['Sage_VAT_Amount'].sum()
gross = remittance_doc.df['Sage_Gross_Amount'].sum()
# Check sage calculations - shouldn't be a problem. if this is passed can then rely on two of the
# three values to set the third. Note due to rounding you can't calculate them except approximately unless
# you have access to the line items.
if (p(net + vat) != p(gross)):
remittance_doc.checked = False
raise PySageError(
"Internal calcs of sum in Sage don't add up. net + vat != gross, {} + {} != {}"
.format(net, vat, gross))
# Check that gross AIS doc values match Sage gross values TODO remove specific for local installation
gross_sum_ex_discount = remittance_doc.df[
remittance_doc.df['Member Code'] !=
'4552']['Sage_Gross_Amount'].sum()
if gross != gross_sum_ex_discount:
remittance_doc.checked = False
raise PySageError(
"Adding up total AIS invoices doesn't equal Sage sum, {} != {}, types {}, {}"
.format(gross_sum_ex_discount, gross,
type(gross_sum_ex_discount), type(gross)))
def test_check_SageImportSuccess(self):
filename = ''
try:
date = dt.datetime.now()
nominal_code = '4009'
value = p(2.45)
si = SageImport()
try:
filename = si.start_file('TestSage2')
# Hopefully writing nonsense so won't fail
si.check_write_row('JD', si.default_bank, 'Discount', date,
'CN Discount for Ord42', value, 'T9')
si.check_write_row('JC', '0021', 'Discount', date,
'CN Discount for Ord42', value, 'T9')
finally:
si.close_file()
assert os.path.isfile(filename)
finally:
remove_old_sage_import_files(filename, 'TestSage2 Import.csv.last')
def detailed_check_for_transactions_in_the_month(self, journal_type, account, date, details):
en = date + pd.offsets.MonthEnd(0)
st = en - pd.offsets.MonthBegin(1)
test1 = self.sqldata[self.sqldata['ACCOUNT_REF'] == int(account)]
test2 = test1[test1['DATE'] >= st]
test3 = test2[test2['DATE'] <= en]
test = test3[test3['DETAILS'] == details] # Exact match is ok since looking for machine duplicates
l = len(test)
if l == 0:
comment = 'Found no transactions from {} upto {} .'.format(
st.strftime('%Y-%m-%d'), en.strftime('%Y-%m-%d'), )
return (False, 0, comment)
else:
tn = test[:1]
comment = 'Found {} transactions from {} upto {}. First was on {}: details {}: for {}.'.format(
l, st.strftime('%Y-%m-%d'), en.strftime('%Y-%m-%d'),
list(tn['DATE'])[0].strftime('%Y-%m-%d'),
list(tn['DETAILS'])[0],
p(list(tn['AMOUNT'])[0]),)
return (True, 0, comment)
def test_simple_SageImport(self):
# Todo code test code will fail if run on the end of day so file generated on one day
# and tested on the next
filename = ''
try:
date = dt.datetime.now()
nominal_code = '4009'
value = p(2.45)
si = SageImport()
try:
filename = si.start_file('TestSage')
# Hopefully writing nonsense so won't fail
si.write_row('JD', si.default_bank, 'Discount', date,
'CN Discount for Ord42', value, 'T9')
si.write_row('JC', '0021', 'Discount', date,
'CN Discount for Ord42', value, 'T9')
finally:
si.close_file()
assert os.path.isfile(filename)
finally:
remove_old_sage_import_files(filename, 'TestSage Import.csv.last')