From 93c3a6038d25cd26e18f1c55c5f6417e3903b9da Mon Sep 17 00:00:00 2001 From: Ross Addison Date: Sun, 23 Nov 2014 15:37:31 +0100 Subject: [PATCH] Initial module version Signed-off-by: Janusz Dobrowolski --- _init/config | 19 + hooks.php | 29 + import_multijournalentries.php | 279 ++++++++++ includes/import_multijournalentries.inc | 678 ++++++++++++++++++++++++ templates/bank.csv | 5 + templates/deposit.csv | 3 + templates/journal.csv | 10 + templates/payment.csv | 3 + 8 files changed, 1026 insertions(+) create mode 100644 _init/config create mode 100644 hooks.php create mode 100644 import_multijournalentries.php create mode 100644 includes/import_multijournalentries.inc create mode 100644 templates/bank.csv create mode 100644 templates/deposit.csv create mode 100644 templates/journal.csv create mode 100644 templates/payment.csv diff --git a/_init/config b/_init/config new file mode 100644 index 0000000..d991fac --- /dev/null +++ b/_init/config @@ -0,0 +1,19 @@ +Package: import_transactions +Version: 2.3.22-1 +Name: Import Transactions +Description:Import transactions from csv in journal format, payment format, deposit format, or adapted bank statement format. +Features include a trial check before importing, tabular display of journal entries, importing of bank statements with additional +columns for spreadsheet adjusted associated transaction account codes, customer, or supplier id's, additional tax type column +for VAT registered companies for vat inclusive income or expenses, inclusion of transactions automatically in an audit trail, +display notifications identifying how tables within the database are being affected for a more transparent display, +additional lookup tools for looking up customer id's, supplier id's, company setup information eg. fiscal year, +tools that users will find useful for inclusion in their spreadsheet prior to conversion to csv, +and suitable validation checks for customers, suppliers, tax codes, references. + +Author: Ross Addison +Maintenance: Ross Addison +Homepage: frontaccounting.com +Depends: +Type: extension +InstallPath: modules/import_transactions + diff --git a/hooks.php b/hooks.php new file mode 100644 index 0000000..4bc8560 --- /dev/null +++ b/hooks.php @@ -0,0 +1,29 @@ +id) { + case 'GL': + $app->add_rapp_function(2, _('Import &Multiple Jourmal Entries'), + $path_to_root.'/modules/import_multijournalentries/import_multijournalentries.php', 'SA_CSVMULTIJOURNALIMPORT'); + } + } + + function install_access() + { + $security_sections[SS_IMPORTMULTIJOURNAL] = _("Import Multiple Journal Entries"); + + $security_areas['SA_CSVMULTIJOURNALIMPORT'] = array(SS_IMPORTMULTIJOURNAL|101, _("Import Multiple Journal Entries")); + + return array($security_areas, $security_sections); + } +} +?> \ No newline at end of file diff --git a/import_multijournalentries.php b/import_multijournalentries.php new file mode 100644 index 0000000..f293b94 --- /dev/null +++ b/import_multijournalentries.php @@ -0,0 +1,279 @@ + 0) && isset($_POST['stateformat'])) {$stateformat = $_POST['stateformat'];} else {$stateformat = null;} + $errCnt = 0; + $entryCount = 0; + $bank_account_gl_code=""; + $error = false; + $displayed_at_least_once = false; + if (isset($_FILES['imp']) && $_FILES['imp']['name'] != '') + { + $filename = $_FILES['imp']['tmp_name']; + $sep = $_POST['sep']; + if (isset($_POST['bank_account']) ? $_POST['bank_account'] : ""){ + $bank_account = isset($_POST['bank_account']) ? $_POST['bank_account'] : ""; + $bank_account_gl_code = get_bank_gl_account($bank_account);} //gl_db_bank_accounts.inc + $fp = @fopen($filename, "r"); + if (!$fp) + { + display_error(_("Error opening file $filename")); + } + else + { + begin_transaction(); + $curEntryId=last_transno($type)+1; + $line = 0; + $trial=!null; + $description = ""; + $i=0; + $total_debit_positive=0; + $total_credit_negative=0; + $input_id=1; + $skippedheader = false; + $prev_ref = null; + $prev_date = null; + $bank_desc = ""; + $ignore = ""; + $debitsEqualcredits = 1; + while ($data = fgetcsv($fp, 4096, $sep)) + { + if (($line++ == 0) && ($skippedheader == false)) + {display_notification_centered(_("Skipped header. (line $line in import file '{$_FILES['imp']['name']}')"));$skippedheader = true;continue;} + display_notification_centered(" --------------------------------------------------------------------------------------------Line $line ------------------------------------------------------------------------------------------"); + + if ($type == 0) // if type is a journal + { + list($reference, $date, $memo, $amt, $code_id, $taxtype, $dim1_ref, $dim2_ref,$person_type_id,$person_id) = $data; + $memo = $memo ." Date: ".$date." Reference: ".$reference; + } + else + { + if (($type == ST_BANKPAYMENT) && ($stateformat!=null)) + //All amounts to the right of amt are ignored since only considering payments which are to the left of deposits on a bank statement. + { + list($reference, $date, $memo, $amt, $ignore, $code_id, $taxtype, $dim1_ref, $dim2_ref,$person_type_id,$person_id) = $data; + if ((($ignore == "")||($ignore == null)) && ($amt > 0.01 )){} else + { + display_notification_centered(_("Ignoring deposit. Use same csv under deposit processing. (line $line in import file '{$_FILES['imp']['name']}')")); + $error = false; + $prev_ref = $reference; + continue; + } + } + if (($type == ST_BANKDEPOSIT) && ($stateformat!=null)) + { + //All amounts to the left of amt are ignored since only considering deposits which are to the left of payments on a bank statement. + list($reference, $date, $memo, $ignore, $amt, $code_id, $taxtype, $dim1_ref, $dim2_ref,$person_type_id,$person_id) = $data; + if ((($ignore == "")||($ignore == null)) && ($amt > 0.01 )){} else + { + display_notification_centered(_("Ignoring payment. Use same csv under payment processing.(line $line in import file '{$_FILES['imp']['name']}')")); + $error = false; + $prev_ref = $reference; + continue; + } + } + if ((($type == ST_BANKDEPOSIT) || ($type == ST_BANKPAYMENT)) && ($stateformat==null)) + list($reference, $date, $memo, $amt, $code_id, $taxtype, $dim1_ref, $dim2_ref,$person_type_id,$person_id) = $data; + + } + if ($prev_ref <> $reference) { + init_entry_part_2($entry, $date, $reference);} + + if ($type == 0) + { + list($error,$input_id, $curEntryId,$total_debit_positive,$total_credit_negative)=journal_id($prev_date,$date,$amt, $input_id, $total_debit_positive, $total_credit_negative, $line); + } + + list($error,$memo)=check_customer_supplier($code_id,$person_id,$person_type_id,$line,$memo,$error); + $dim1 = get_dimension_id_from_dimref($dim1_ref); + if ($dim1_ref != '' && $dim1 == null) {display_error(_("Error: Could not find dimension with dimension reference '$dim1_ref' (line $line in import file '{$_FILES['imp']['name']}')"));$error = true;} + $dim2 = get_dimension_id_from_dimref($dim2_ref); + if ($dim2_ref != '' && $dim2 == null) {display_error(_("Error: Could not find dimension with dimension reference '$dim2_ref' (line $line in import file '{$_FILES['imp']['name']}')"));$error = true;} + if ($reference == '' ){display_error(_("$line does not have a reference. (line $line in import file '{$_FILES['imp']['name']}')"));$error = true;} + if (($Refs->exists($type, $reference)) && ($reference!=$prev_ref)){ + display_error(_("Error: Reference from table 'refs': '$reference' is already in use. (line $line in import file '{$_FILES['imp']['name']}')"));$error = true;} + elseif (($Refs->exists($type, $reference)) && ($reference==$prev_ref)){//do nothing $Refs->save($type,$line,$reference); + }elseif ((($Refs->exists($type, $reference))==null) && ($reference!=$prev_ref)){ + $Refs->save($type,$curEntryId,$reference); + save_next_reference($type, $reference); + } + + $description = get_gl_account_name($code_id); + if (is_date($date)==false) + { + display_error(_("Error: date '$date' not properly formatted (line $line in import file '{$_FILES['imp']['name']}')")); + $error = true; + } + //$date = sql2date($date); + if ((is_date_in_fiscalyear($date)) == false) {display_error(_("Error: Date not within company fiscal year. Make sure date is in dd/mm/yyyy format and your csv years are 4 digits long."));$error=true;} + // validation for + + if (($type == 1) || ($type ==2)) {$bankdesc = get_gl_account_name($bank_account_gl_code);} + + + $i=journal_display($i, $type, $taxtype, $amt, $entry, $code_id, $dim1, $dim2, $memo, $description, $bank_account_gl_code, $bank_desc); + if (!$error) + { + if (($type == ST_JOURNAL)) + { + if (gl_account_in_bank_accounts($code_id) == true){display_notification_centered(_("Error: Bank account detected in journal. No processing of bank accounts allowed. (line $line in import file '{$_FILES['imp']['name']}')"));$error = true;} + if (check_tax_appropriate($code_id, $taxtype,$line) == true) + { + journal_inclusive_tax($type,$date, $line, $curEntryId, $code_id, $dim1, $dim2, $memo, $amt, $taxtype,$person_type_id,$person_id); + add_audit_trail($type, $curEntryId, $date); + } + + } + elseif (($type == ST_BANKDEPOSIT || $type == ST_BANKPAYMENT) && ($amt > 0)) + { + if (check_tax_appropriate($code_id, $taxtype, $line) == true) + { + bank_inclusive_tax($type, $reference, $date, $bank_account, $bank_account_gl_code, $line, $curEntryId, $code_id, $dim1, $dim2, $memo, $amt, $taxtype,$person_type_id,$person_id); + + } + else + { + display_notification_centered(_("Warning: Taxtype used with Asset or Liability - $curEntryId, $date, $code_id.(line $line in import file '{$_FILES['imp']['name']}')")); + } + } + + elseif (($type == ST_BANKDEPOSIT || $type == ST_BANKPAYMENT) && ($amt < 0)) + { + display_notification_centered(_("Error: Credit amounts represented by negative amounts being entered. Check csv file is correct.(line $line in import file '{$_FILES['imp']['name']}')")); + $error = true; + } + $entryCount = $entryCount+1; + } + + if ($error) {$errCnt=$errCnt+1;} + $error = false; + $prev_ref = $reference; + $prev_date = $date; + $curEntryId = $curEntryId + 1; + }//while + $displayed_at_least_once = display_entries($type, $entry); + end_row(); + end_table(1); + div_end(); + + + if ($displayed_at_least_once == false) //there has been no occurance of debits equaling credits - at least one journal not properly balanced + { + display_notification_centered(_("Error: Debits do not equal credits.")); + $errCnt = $errCnt + 1; + } // +// Commit import to database + if ((isset($_POST['trial'])) && ($_POST['trial']==null));{ + $trial = null;} + if (isset($_POST['trial']) && ($_POST['trial']!=null)) {$trial = !null;} + if ($type == ST_JOURNAL){$typeString = "Journals";} + elseif ($type == ST_BANKDEPOSIT){$typeString = "Deposits";} + elseif ($type == ST_BANKPAYMENT){$typeString = "Payments";} +if (($errCnt==0) && ($trial == null)) + {if ($entryCount > 0){commit_transaction();display_notification_centered(_("$entryCount $typeString have been imported."));} + else display_error(_("Import file contained no $typeString."));} +if (($errCnt==0) && ($trial == !null)) + {display_notification_centered(_("$entryCount $typeString would have been successful if imported. Uncheck Trial check before importing."));} +if (($errCnt>0) && ($trial == !null) && $displayed_at_least_once) + {display_notification_centered(_("$errCnt error(s) detected. Correct before importing."));} + if (($errCnt>0) && ($trial == null) && $displayed_at_least_once) + {display_notification_centered(_("$errCnt error(s) detected. Correct before importing."));} + $errCnt =0; + + }// if (!$fp) + + + }// if (isset($_FILES['imp']) && $_FILES['imp']['name'] != '') +@fclose($fp); + + +}// if (isset($_POST['type'])) + +// User Interface +start_form(true); +div_start('_main_table'); +initialize_controls(); +start_outer_table(TABLESTYLE2, "width=95%");//outer table +start_table(TABLESTYLE2,"width=95%");//inner table +$type=show_table_section_import_settings(); +show_table_section_control_accounts(); +show_table_section_display($type); +if ((($type != ST_JOURNAL))) +{ + $stateformat = show_table_section_bankstatement_checkbox(); +} +show_table_section_csv_separator(); +show_table_section_trial_or_final(); +end_table(1); +submit_center('import', "Process",$echo=true, $title=false, $async=true, $icon=ICON_OK); +end_outer_table(1); +div_end('_main_table'); +end_form(); +div_end('_page_body'); //div crucial for specific page updates through Ajax +end_page(); +?> \ No newline at end of file diff --git a/includes/import_multijournalentries.inc b/includes/import_multijournalentries.inc new file mode 100644 index 0000000..ea3f343 --- /dev/null +++ b/includes/import_multijournalentries.inc @@ -0,0 +1,678 @@ + 0) + { + display_notification(_("Deleted ".db_num_rows($result1a)." records from table gl_trans.")); + $sql1b = "DELETE FROM ".TB_PREF."gl_trans"; + $result1b = db_query($sql1b, _("Could not delete records.")); + } + + //bank entries table: bank_trans + $sql2a = "SELECT * FROM ".TB_PREF."bank_trans WHERE id>-1"; + $result2a = db_query($sql2a, _("Could not query bank_trans")); + if (db_num_rows($result2a) > 0) + { + display_notification(_("Deleted ".db_num_rows($result2a)." records from table bank_trans.")); + $sql2b = "DELETE FROM ".TB_PREF."bank_trans"; + $result2b=db_query($sql2b, _("Could not delete records.")); + } + + //tax entries table: trans_tax_details + $sql3a = "SELECT * FROM ".TB_PREF."trans_tax_details WHERE id>-1"; + $result3a = db_query($sql3a, _("Could not query bank_trans")); + if (db_num_rows($result3a) > 0) + { + display_notification(_("Deleted ".db_num_rows($result3a)." records from table trans_tax_details.")); + $sql3b = "DELETE FROM ".TB_PREF."trans_tax_details"; + $result3b=db_query($sql3b, _("Could not delete records.")); + } + + //next_reference entries table: sys_types + $sql4 = "UPDATE ".TB_PREF."sys_types SET next_reference='1'"; + $result4=db_query($sql4, _("Could not update records.")); + display_notification(_("Reset column 'next_reference' in table 'sys_types' to '1' for all types.")); + + //refs + $sql5a = "SELECT * FROM ".TB_PREF."refs"; + $result5a=db_query($sql5a, _("Could not query bank_trans")); + if (db_num_rows($result5a) > 0) + { + display_notification(_("Deleted ".db_num_rows($result5a)." records from table refs")); + $sql5b = "DELETE FROM ".TB_PREF."refs"; + $result5b=db_query($sql5b, _("Could not delete records.")); + } + + } + +} + +function bank_inclusive_tax($type, $reference, $date, $bank_account, $bank_account_gl_code, $line, $curEntryId, $code, $dim1, $dim2, $memo, $amt, $taxtype, $person_type_id,$person_id) // extra inclusive of tax column in csv +{ + list($return_amt,$inclusive_amt,$exclusive_amt,$taxamt,$taxaccount,$salestaxaccount,$purchasetaxaccount)=determinetax($taxtype, $amt); + if ($type == ST_BANKDEPOSIT) + { + add_bank_trans($type, $curEntryId, $bank_account, $reference, $date, $inclusive_amt, $person_type_id, $person_id,$currency="", $err_msg="", $rate=0); + display_notification_centered(_("Added to table 'bank_trans': $curEntryId, $date, $reference, $code, $dim1, $dim2, $inclusive_amt (line $line in import file '{$_FILES['imp']['name']}')")); + + if ($taxtype > 0) + { + + add_gl_trans($type,$curEntryId, $date, $code, $dim1, $dim2 ,$memo, -$exclusive_amt, $currency=null, $person_type_id,$person_id, $err_msg="", $rate=0); + add_audit_trail($type, $curEntryId, $date); + display_notification_centered(_("Added to table 'gl_trans Credit:': $curEntryId, $date, $code, $dim1, $dim2, -$exclusive_amt, $memo (line $line in import file '{$_FILES['imp']['name']}')")); + + add_gl_trans($type,$curEntryId, $date, $salestaxaccount, $dim1, $dim2 ,$memo, -$taxamt, $currency=null, $person_type_id,$person_id, $err_msg="", $rate=0); + add_audit_trail($type, $curEntryId, $date); + display_notification_centered(_("Added tax to table 'gl_trans Credit:': $curEntryId, $date, $salestaxaccount, $dim1, $dim2, -$taxamt, $memo (line $line in import file '{$_FILES['imp']['name']}')")); + } + + if ($taxtype == 0) + { + add_gl_trans($type,$curEntryId, $date, $code, $dim1, $dim2 ,$memo, -$inclusive_amt, $currency=null, $person_type_id,$person_id, $err_msg="", $rate=0); + add_audit_trail($type, $curEntryId, $date); + display_notification_centered(_("Added to table 'gl_trans Credit:': $curEntryId, $date, $code, $dim1, $dim2, -$inclusive_amt, $memo (line $line in import file '{$_FILES['imp']['name']}')")); + } + + add_gl_trans($type, $curEntryId, $date, $bank_account_gl_code, $dim1, $dim2 ,$memo, $inclusive_amt, $currency=null, $person_type_id, $person_id, $err_msg="", $rate=0); + add_audit_trail($type, $curEntryId, $date); + display_notification_centered(_("Added to table 'gl_trans' Debit bank account: $curEntryId, $date, $bank_account_gl_code, $dim1, $dim2, $inclusive_amt, $memo (line $line in import file '{$_FILES['imp']['name']}')")); + add_gl_tax_details($salestaxaccount, ST_BANKDEPOSIT, $curEntryId, -$taxamt, 1, $date, $memo,1, -$inclusive_amt); + } + If ($type == ST_BANKPAYMENT) + { + add_bank_trans($type, $curEntryId, $bank_account, $reference, $date, -$inclusive_amt, $person_type_id, $person_id,$currency="", $err_msg="", $rate=0); + display_notification_centered(_("Added to table 'bank_trans': $curEntryId, $date, $reference, $code, $dim1, $dim2, -$inclusive_amt (line $line in import file '{$_FILES['imp']['name']}')")); + + + if ($taxtype > 0) + { + add_gl_trans($type,$curEntryId, $date, $code, $dim1, $dim2 ,$memo, $exclusive_amt, $currency=null, $person_type_id,$person_id, $err_msg="", $rate=0); + add_audit_trail($type, $curEntryId, $date); + display_notification_centered(_("Added to table 'gl_trans' Debit: $curEntryId, $date, $code, $dim1, $dim2, $exclusive_amt, $memo (line $line in import file '{$_FILES['imp']['name']}')")); + + add_gl_trans($type,$curEntryId, $date, $purchasetaxaccount, $dim1, $dim2 ,$memo, $taxamt, $currency=null, $person_type_id,$person_id, $err_msg="", $rate=0); + add_audit_trail($type, $curEntryId, $date); + display_notification_centered(_("Added tax to table 'gl_trans' Debit: $curEntryId, $date, $purchasetaxaccount, $dim1, $dim2, $taxamt, $memo (line $line in import file '{$_FILES['imp']['name']}')")); + } + if ($taxtype == 0) + { + add_gl_trans($type,$curEntryId, $date, $code, $dim1, $dim2 ,$memo, $inclusive_amt, $currency=null, $person_type_id,$person_id, $err_msg="", $rate=0); + add_audit_trail($type, $curEntryId, $date); + display_notification_centered(_("Added to table 'gl_trans' Debit: $curEntryId, $date, $code, $dim1, $dim2, $inclusive_amt, $memo (line $line in import file '{$_FILES['imp']['name']}')")); + } + add_gl_trans($type,$curEntryId, $date, $bank_account_gl_code, $dim1, $dim2 ,$memo, -$inclusive_amt, $currency=null,$person_type_id, $person_id, $err_msg="", $rate=0); + add_audit_trail($type, $curEntryId, $date); + display_notification_centered(_("Added to table 'gl_trans' Credit bank account: $curEntryId, $date, $bank_account_gl_code, $dim1, $dim2, -$inclusive_amt, $memo (line $line in import file '{$_FILES['imp']['name']}')")); + add_gl_tax_details($purchasetaxaccount, ST_BANKPAYMENT, $curEntryId, $taxamt, 1, $date, $memo,1, $inclusive_amt); + } + } + +//Note: Ajax will be implemented here since default is true +function checkbox_row($label, $name, $value, $submit_on_change=true, $title, $params = "") //adapted from check_row to accomodate colspan +{ + if (is_array($label)) + { + echo "
    "; + foreach ($label as $line => $item) {echo "
  • $item
    • ";} + echo "
"; + } + else + + { + echo "$label"; + } + echo ""; + echo checkbox("", $name, $value, $submit_on_change=true, $title); + echo ""; + echo "\n"; +} +//function checkbox_row($label, $name, $value, $params='', $submit_on_change, $title) //adapted from check_row to accomodate colspan + +//notify when tax type used with income or expenses. +//issue warning if 'ie' is returned false => tax type used with asset or liability. +function check_tax_appropriate($code, $taxtype, $line) +{ + $ie = true; + if ($taxtype > 0) + {//get account_code and account_type from chart master + $sql = "SELECT account_code,account_type FROM ".TB_PREF."chart_master WHERE ".$code." = account_code"; + $result = db_query($sql, _("Could not query chart_master table")); + $row = db_fetch($result); + $acctype = $row['account_type']; + $sql2 = "SELECT id, class_id FROM ".TB_PREF."chart_types WHERE ".$acctype." = id"; + $result2 = db_query($sql2, _("Could not query chart_types table")); + $row2 = db_fetch($result2); + $classid = $row2['class_id']; + + //if classid < 3 => asset or liability + + if ($classid > 2) {$ie = true;display_notification_centered(_("Correct: Tax type used with Income or expense:$code. (line $line in import file '{$_FILES['imp']['name']}')"));} else {$ie = false; display_notification_centered(_("Warning: Taxtype used with Asset or Liability.(line $line in import file '{$_FILES['imp']['name']}')"));} + + } + return $ie; +} + +function check_customer_supplier($code_id,$person_id,$person_type_id,$line,$memo,$error) +{ + if ($person_id = !null) + { + //check that debtor or supplier exist + if (($code_id==$_POST['debtors_act']) && ($person_type_id==2) && (customer_exist($person_id)==false)){display_notification_centered(_("Error: Customer does not exist. (line $line in import file '{$_FILES['imp']['name']}') "));$error=true;} + if (($code_id==$_POST['creditors_act']) && ($person_type_id==1) && (supplier_exist($person_id)==false)){display_notification_centered(_("Error: Supplier does not exist. (line $line in import file '{$_FILES['imp']['name']}') "));$error=true;} + //build a memo on current memo if entry line acceptable + if (($code_id==$_POST['debtors_act']) && ($person_type_id==2) && (customer_exist($person_id)==true)) + { + $memo = $memo." Customer name: ".customer_name($person_id)." id: ".$person_id; + } + if (($code_id==$_POST['creditors_act']) && ($person_type_id==1) && (supplier_exist($person_id)==true)) + { + $memo = $memo." Supplier name: ".supplier_name($person_id)." id: ".$person_id; + } + } + //check for default control accounts used without $person_id and $person_type_id + if (($code_id==$_POST['debtors_act']) && (($person_id==null) || ($person_type_id==null))){display_notification_centered(_("Error: Debtors Control account used without person_type_id or person_id. (line $line in import file '{$_FILES['imp']['name']}') "));$error=true;} + if (($code_id==$_POST['creditors_act']) && (($person_id==null) || ($person_type_id==null))){display_notification_centered(_("Error: Creditors Control account used without person_type_id or person_id. (line $line in import file '{$_FILES['imp']['name']}') "));$error=true;} + +return array($error,$memo); +} +//csv_demo_line("Example Journal Dr:","this", "16/12/2014", $lastjlref, "8100", "D1", "D2", "222.24"); +function csv_demo_line($param="",$count,$a,$b,$c,$d,$e,$f,$g,$h,$i,$j,$k,$l,$m) //utilized for displaying examples in a table +{ + start_row($param); // param usefull for incorporating css classes. + label_cell($a,"width=3% wrap",$count+1); + label_cell($b,"width=2% wrap",$count+2); + label_cell($c,"width=2% wrap",$count+3); + label_cell($d,"width=2% wrap",$count+4); + label_cell($e,"width=2% wrap",$count+5); + label_cell($f,"width=2% align=right wrap",$count+6); + label_cell($g,"width=2% wrap",$count+7); + label_cell($h,"width=2% wrap",$count+8); + label_cell($i,"width=2% wrap",$count+9); + label_cell($j,"width=2% wrap",$count+10); + label_cell($k,"width=2% wrap",$count+11); + label_cell($l,"width=2% wrap",$count+12); + label_cell($m,"width=2% wrap",$count+13); + end_row(); +} + +function customer_exist($person_id) +{ + $sql= "SELECT debtor_no FROM ".TB_PREF."debtors_master WHERE debtor_no=".db_escape($person_id); + $result = db_query($sql, _("Could not query debtors master table")); + if (db_num_rows($result) > 0) {return true;} else {return false;} +} + +function customer_name($person_id) +{ + $sql= "SELECT name FROM ".TB_PREF."debtors_master WHERE debtor_no=".db_escape($person_id); + $result = db_query($sql, _("Could not query debtors master table")); + $row = db_fetch($result); + $custname = $row['name']; + return $custname; +} + +//if taxtype > 0 separate into inclusive, exclusive, and tax in preparation for gl split +function determinetax($taxtype,$amt) +{ + $initialamt = $amt; + $sql = "SELECT * FROM ".TB_PREF."tax_types WHERE id=".db_escape($taxtype); + $result = db_query($sql, _("Could not query tax type table")); + $row = db_fetch($result); + $taxper = $row['rate']; + //determine the VAT accounts associated with the taxtype eg. standard VAT + $salestaxaccount = $row['sales_gl_code']; + $purchasetaxaccount = $row['purchasing_gl_code']; + $taxamt = round(($amt/(1+($taxper/100))*($taxper/100)),2); + if ($taxamt < 0){$taxaccount = $salestaxaccount;}else{$taxaccount = $purchasetaxaccount;} + $inclusive_amt = round($amt,2); + //get the exclusive value + $amt=round($amt/(1+($taxper/100)),2); + //avoid rounding issues with the following line + $exclusive_amt = $inclusive_amt - $taxamt; + return array($amt,$inclusive_amt,$exclusive_amt, $taxamt, $taxaccount, $salestaxaccount, $purchasetaxaccount); +} + +function display_import_items($title, $order) //adapted from display_gl_items +{ + global $path_to_root; + $displayed_at_least_once=false; + display_heading($title); + $dim = get_company_pref('use_dimension'); + div_start('items_table'); + start_table(TABLESTYLE2, "colspan=7 width=95%"); + if ($dim == 2) + $th = array(_("Account Code"), _("Account Description"), _("Dimension")." 1",_("Dimension")." 2", _("Debit"), _("Credit"), _("Memo"),_("Jnl Entry")); + + else if ($dim == 1)$th = array(_("Account Code"), _("Account Description"), _("Dimension"),_("Debit"), _("Credit"), _("Memo"),_("Jnl Entry")); + + else $th = array(_("Account Code"), _("Account Description"),_("Debit"), _("Credit"), _("Memo"), _("Jnl Entry")); + + if (count($order->gl_items)) $th[] = ''; + table_header($th); +$k = 0; +$id = find_submit('Edit'); +$num = 0; +foreach ($order->gl_items as $line => $item) +{ + if ($id != $line){ + $num=$num+1; + alt_table_row_color($k); + label_cell($item->code_id, "width = 10%"); + label_cell($item->description,"width = 30%"); + if ($dim >= 1){label_cell(get_dimension_string($item->dimension_id, true),"wrap width = 10%");} + if ($dim > 1){label_cell(get_dimension_string($item->dimension2_id, true),"wrap width = 10%");} + if ($item->amount > 0) { + amount_cell(abs($item->amount),null,"width=10%"); + label_cell("","width = 10%"); } + //note: the $item->reference is carrying $memo for display purposes. + else {label_cell("","width = 10%");amount_cell(abs($item->amount),null,"width=10%");}label_cell($item->reference,"wrap align=left width=20%");label_cell($num,"width=5% align=right colspan=3"); + end_row(); + } +} +if ($order->count_gl_items()) { + $colspan = ($dim == 2 ? "4" : ($dim == 1 ? "3" : "2")); + start_row(); + label_cell(_("Total"), "align=right colspan=" . $colspan); + amount_cell($order->gl_items_total_debit()); + amount_cell(abs($order->gl_items_total_credit())); + label_cell('', "colspan=4"); + end_row(); + $displayed_at_least_once = true; + +} +end_table(); +echo "
"; +div_end(); +return $displayed_at_least_once; +}// display import itemsmport items + +function display_entries($type,$entry) +{ + if ($type == 0) $jname = "Journal Processing"; + if ($type == 1) $jname = "Payment Processing"; + if ($type == 2) $jname = "Deposit Processing"; + $title = $jname; + return $displayed_at_least_once = display_import_items(_("$title"), $entry); +} + +function get_dimension_id_from_dimref($ref) +{ + if ($ref == null || trim($ref) == '') + return 0; + + $sql = "SELECT id FROM ".TB_PREF."dimensions WHERE reference LIKE ".db_escape($ref); + + $result = db_query($sql, _("could not get dimension from reference")); + + $row = db_fetch_row($result); + + return $row[0]; +}//function get_dimension_id_from_reference($ref) +// + +//$entry or item_cart class merely used for tabular display of journal entries. +function init_entry_part_1($entry) +{ + $entry->clear_items(); + //group all csv lines 'gl_items' under one entry + $entry->order_id = 0; +} + +function init_entry_part_2($entry,$date,$reference) +{ + $entry->tran_date = $date; + $entry->reference = $reference; + $entry->memo_ = 'Imported via \'Import Multiple Journal Entries\' plugin'; +} +function import_type_list_row($label, $name, $selected=null, $submit_on_change=false) +{ + $arr = array( + ST_JOURNAL=> _("Journal Processing"), + ST_BANKDEPOSIT=> _("Deposit Processing"), + ST_BANKPAYMENT=> _("Payment Processing") + ); + //echo "$label"; + echo "$label"; + echo array_selector($name, $selected, $arr, + array( + 'select_submit'=> $submit_on_change, + 'async' => false, + )); + echo "\n"; +}//function import_type_list_row($label, $name, $selected=null, $submit_on_change=false) + +function initialize_controls() +{ + initialize_main_controls(); + initialize_tools_controls(); +} + +function initialize_main_controls() +//initialize dropdown boxes +{ +$myrow = get_company_prefs(); +if (!isset($_POST['debtors_act'])) $_POST['debtors_act'] = $myrow["debtors_act"]; +if (!isset($_POST['creditors_act'])) $_POST['creditors_act'] = $myrow["creditors_act"]; +if (!isset($_POST['type'])) $_POST['type'] = null; +if (!isset($_POST['stateformat'])) {$stateformat = null;$_POST['stateformat']=null;} +if (!isset($_POST['tools'])) $_POST['tools']=null; +if (!isset($_POST['lookup'])) $_POST['lookup']=null; +if (!isset($_POST['notes'])) $_POST['notes']=null; +if (!isset($_POST['sep'])) $_POST['sep'] = ","; +if (!isset($_POST['bank_account'])) $_POST['bank_account'] = _("Current account"); +if (!isset($_FILES['imp'])) $_FILES['imp'] = null; +if (!isset($_POST['trial'])) $_POST['trial'] = !null; +} + +function initialize_tools_controls() +{ +if (!isset($_POST['cust'])) $_POST['cust'] = null; +if (!isset($_POST['supp'])) $_POST['supp'] = null; +if (!isset($_POST['fis'])) $_POST['fis'] = null; +if (!isset($_POST['ttype'])) $_POST['ttype'] = null; +if (!isset($_POST['lookrefs'])) $_POST['lookrefs'] = _("Select a reference"); +} + + +function journal_display($i,$type,$taxtype,$amt,$entry,$code_id, $dim1, $dim2, $memo, $description,$bank_account_gl_code,$bankdesc) +{ + if ($taxtype > 0) //tax inclusive + { + list($return_amt, $inclusive_amt,$exclusive_amt,$taxamt,$taxaccount,$salestaxaccount, $purchasetaxaccount)=determinetax($taxtype,$amt); + $taxdesc = get_gl_account_name($taxaccount); + if ($type == 0) //tax inclusive and journal line + { + $entry->gl_items[$i] = new gl_item($code_id, $dim1, $dim2, $exclusive_amt, $memo, $description); + $i=$i+1; + $entry->gl_items[$i] = new gl_item($taxaccount, $dim1, $dim2, $taxamt, $memo, $taxdesc); + } + + if ($type == 1) //tax inclusive and bank payment + { + $entry->gl_items[$i] = new gl_item($code_id, $dim1, $dim2, $exclusive_amt, $memo, $description); + $i=$i+1; + $entry->gl_items[$i] = new gl_item($taxaccount, $dim1, $dim2, $taxamt, $memo, $taxdesc); + $i=$i+1; + $entry->gl_items[$i] = new gl_item($bank_account_gl_code, $dim1, $dim2, -$amt, $memo, $bankdesc); + } + + if ($type == 2) //tax inclusive and bank deposit + { + $entry->gl_items[$i] = new gl_item($code_id, $dim1, $dim2, -$exclusive_amt, $memo, $description); + $i=$i+1; + $entry->gl_items[$i] = new gl_item($taxaccount, $dim1, $dim2, -$taxamt, $memo, $taxdesc); + $i=$i+1; + $entry->gl_items[$i] = new gl_item($bank_account_gl_code, $dim1, $dim2, $amt, $memo, $bankdesc); + } + } + + if ($taxtype == 0) + { + if ($type == 0) //tax exclusive and journal line + { + $entry->gl_items[$i] = new gl_item($code_id, $dim1, $dim2, $amt, $memo, $description); + } + + if ($type == 1) //tax exclusive and bank payment + { + $entry->gl_items[$i] = new gl_item($code_id, $dim1, $dim2, $amt, $memo, $description); + $i=$i+1; + $entry->gl_items[$i] = new gl_item($bank_account_gl_code, $dim1, $dim2, -$amt, $memo, $bankdesc); + } + + if ($type == 2) //tax exclusive and bank deposit + { + $entry->gl_items[$i] = new gl_item($code_id, $dim1, $dim2, -$amt, $memo, $description); + $i=$i+1; + $entry->gl_items[$i] = new gl_item($bank_account_gl_code, $dim1, $dim2, $amt, $memo, $bankdesc); + } + } //if ($taxtype == 0) + + $i=$i+1; + return $i; + +} + +function journal_inclusive_tax($type,$date, $line, $curEntryId, $code, $dim1, $dim2, $memo, $amt, $taxtype=null,$person_type_id,$person_id) // extra inclusive of tax column in csv +{ + list($return_amt,$inclusive_amt,$exclusive_amt,$taxamt,$taxaccount,$salestaxaccount,$purchasetaxaccount)=determinetax($taxtype, $amt); + if (($taxtype > 0 )) //amt inclusive of tax + { + add_gl_trans($type, $curEntryId, $date, $taxaccount, $dim1, $dim2 ,$memo, $taxamt, $currency=null, $person_type_id, $person_id, $err_msg="", $rate=0); //credit tax control account + if ($return_amt<0){display_notification_centered(_("Added tax to table 'gl_trans' Credit: $curEntryId, $date, $taxaccount, $dim1, $dim2, $taxamt, $memo (line $line in import file '{$_FILES['imp']['name']}')"));} + if ($return_amt>0){display_notification_centered(_("Added tax to table 'gl_trans' Debit: $curEntryId,$date, $taxaccount, $dim1, $dim2, $taxamt, $memo (line $line in import file '{$_FILES['imp']['name']}')"));} + add_gl_tax_details($taxaccount, ST_JOURNAL, $curEntryId, $taxamt, 1, $date, $memo,1,$inclusive_amt); + } + add_gl_trans($type, $curEntryId, $date, $code, $dim1, $dim2 ,$memo, $exclusive_amt, $currency=null, $person_type_id,$person_id, $err_msg="", $rate=0); + if ($return_amt >0){display_notification_centered(_("Added to table 'gl_trans' Debit: $curEntryId, $date, $code, $dim1, $dim2, $exclusive_amt, $memo (line $line in import file '{$_FILES['imp']['name']}')"));} + if ($return_amt <0){display_notification_centered(_("Added to table 'gl_trans' Credit: $curEntryId, $date, $code, $dim1, $dim2, $exclusive_amt, $memo (line $line in import file '{$_FILES['imp']['name']}')"));} +} + +function journal_id($prev_date,$date,$amt, $input_id, $total_debit_positive, $total_credit_negative, $line) +{ + $new_id = $input_id; + $error=false; + if ($amt > 0) {$total_debit_positive=$total_debit_positive+$amt;} + if ($amt < 0) {$total_credit_negative=$total_credit_negative+(abs($amt));} + //as soon as debits equal credits create a new journal group id for the next journal line in the csv file + //to be processed + //the current csv line being processed will use the $new_id + if (($total_debit_positive - $total_credit_negative) == 0) + { + if ($prev_date <> $date) {display_notification(_("Error: Dates do not correspond. (line $line in import file '{$_FILES['imp']['name']}')"));$error=true;} + + $total_debit_positive=0; + $total_credit_negative=0; + $input_id = $input_id + 1; + } + return array($error,$input_id, $new_id,$total_debit_positive,$total_credit_negative); +} + +//Output the last transno for bank related transactions using table bank_trans. +//Output the last typeno for general ledger related transactions using table gl_trans. + +function last_transno($type) +{ + //'get last row's typeno since type_no is the trans no' + if ($type == ST_BANKDEPOSIT || $type == ST_BANKPAYMENT) + { + $sql = "SELECT id, type, trans_no, amount FROM ".TB_PREF."bank_trans ORDER BY id DESC LIMIT 1"; + $result = db_query($sql, _("Cannot locate last entry")); + $row = mysql_fetch_assoc($result); + $rowtrans = $row['trans_no']; + } // end if + + if ($type == ST_JOURNAL) + { + $sql = "SELECT counter, type, type_no, amount FROM ".TB_PREF."gl_trans ORDER BY counter DESC LIMIT 1"; + $result = db_query($sql, _("Cannot locate last entry")); + $row = mysql_fetch_assoc($result); + $rowtrans = $row['type_no']; + } // end if + return $rowtrans;// +}//function last_transno($type) + +function processing($type,$errCnt,$entryCount,$displayed_at_least_once,$trial) //to be used +{ +if ($type == ST_JOURNAL){$typeString = "Journals";} + elseif ($type == ST_BANKDEPOSIT){$typeString = "Deposits";} + elseif ($type == ST_BANKPAYMENT){$typeString = "Payments";} +if (($errCnt==0) && ($trial == null)) + {if ($entryCount > 0){commit_transaction();display_notification_centered(_("$entryCount $typeString have been imported."));} + else display_error(_("Import file contained no $typeString."));} +if (($errCnt==0) && ($trial == !null)) + {display_notification_centered(_("$entryCount $typeString would have been successful if imported. Uncheck Trial check before importing."));} +if (($errCnt>0) && ($trial == !null) && $displayed_at_least_once) + {display_notification_centered(_("Errors detected. Correct before importing."));} + if (($errCnt>0) && ($trial == null) && $displayed_at_least_once) + {display_notification_centered(_("Errors detected. Correct before importing."));} + $errCnt =0; +} + +function reference_type_list_cell($label, $name, $selected=null, $submit_on_change=false) +{ + global $systypes_array; + $arr = $systypes_array; //sysnames.inc + echo "$label"; + echo array_selector($name, $selected, $arr, + array( + 'select_submit'=> $submit_on_change, + 'async' => false, + )); + echo ""; +} + +function supplier_exist($person_id) +{ + $sql= "SELECT supplier_id FROM ".TB_PREF."suppliers WHERE supplier_id=".db_escape($person_id); + $result = db_query($sql, _("Could not query debtors master table")); + if (db_num_rows($result) > 0) {return true;} else {return false;} +}//function supplier_exist($person_id) + +function supplier_name($person_id) +{ + $sql= "SELECT supp_name FROM ".TB_PREF."suppliers WHERE supplier_id=".db_escape($person_id); + $result = db_query($sql, _("could not query debtors master table")); + $row = db_fetch($result); + $suppname = $row['supp_name']; + return $suppname; +} +//function supplier_name($person_id) + +function show_table_section_bank_account_number() +{ + $bank = $_POST['bank_account']; + $account = get_bank_gl_account($bank); + table_section_title(_("Bank Accounts"),2); + label_row(_("Account number: "), $account,'colspan=1'); + +} +function show_table_section_company_settings_lookup() +{ + table_section_title(_("Lookup: Company Settings"),2); + echo "";gl_all_accounts_list_cells(_("General Ledger Account Lookup:"), false,false, false, false, _("All Accounts"),$submit_on_change=false);echo ""; + echo "";fiscalyears_list_cells(_("Active Fiscal year(s):"),'fis',false);echo ""; + echo "";dateformats_list_row(_("User selected Display format:"), "date_format", user_date_format());echo ""; + table_section_title(""); +} + +function show_table_section_csv_separator() +{ + table_section_title(_("CSV fields are separated by: "),2); + text_cells(_("Field Separator:"), 'sep', $_POST['sep'], 2, 1,$title="",'colspan=1'); +} +function show_table_section_control_accounts() +{ + table_section_title(_("Control Accounts"),2); + echo "";gl_all_accounts_list_cells(_("Receivable Account / Debtors Control account Default:"), 'debtors_act',$_POST['debtors_act']);echo ""; + echo "";gl_all_accounts_list_cells(_("Payable Account / Creditors Control account Default:"), 'creditors_act',$_POST['creditors_act']);echo ""; +} +function show_table_section_display($type) +{ + global $Ajax; + table_section_title(_("Display"),2); + checkbox_row(_("Lookup id's for Tax Type, Customers, and Suppliers"), 'tools', $_POST['tools'], $submit_on_change=true,$title="",'colspan=1'); + if ((isset($_POST['tools'])) && ($_POST['tools'] !=null)) {show_table_section_tools();} else $Ajax->activate('_page_body'); + checkbox_row(_("Display Company Settings Lookup"), 'lookup', $_POST['lookup'], $submit_on_change=true,$title="",'colspan=1'); + if ((isset($_POST['lookup'])) && ($_POST['lookup'] !=null)) {show_table_section_company_settings_lookup();} else $Ajax->activate('_page_body'); + checkbox_row(_("Display Help Notes"), 'notes', $_POST['notes'], $submit_on_change=true,$title="",'colspan=1'); + if ((isset($_POST['notes'])) && ($_POST['notes'] !=null)) {show_table_section_notes($type);} else $Ajax->activate('_page_body'); + +} + +function show_table_section_import_settings() +{ + table_section_title(_("Import Settings"),2); + import_type_list_row(_("Import Type:"), 'type', $_POST['type'], true); + if ($_POST['type'] != ST_JOURNAL) + {bank_accounts_list_cells( $_POST['type'] == ST_BANKPAYMENT ? _("From:") : _("To:"), 'bank_account',$selected_id="",$_POST['bank_account']); + show_table_section_bank_account_number(); + } + return $type = $_POST['type']; +} +function show_table_section_important_bank() +{ + table_section_title(_("Display Help Notes: Important regarding bank transactions"),2); + label_row(_("1.No negative amounts. Payment amounts are converted to negative amounts for the table 'bank_trans' which represent credit amounts."),"",'colspan=1'); + label_row(_("2.Tax inclusive expenses (eg.rent) or tax inclusive income (eg. sales) must include a taxtype if a company is registered for VAT."),"",'colspan=1'); + label_row(_("3.Dim1_ref is a description for the dimension id. "),"",'colspan=1'); + label_row(_("4.Non-essential fields: dim1_ref, dim2_ref. Include as column headers in csv even if not used."),"",'colspan=1'); + label_row(_("5.The reference field is stored in a separate database 'refs' and should be unique to every transaction."),"",'colspan=1'); + label_row(_("6. Ensure that your csv file date format includes a 4 number year ie. YYYY. Use a low-level format editor to confirm this since format can be lost when saving to csv format. So check your csv with Notepad+ for a four digit year."),"",'colspan=1'); + label_row(_("7. Ensure that 'Account code' in your spreadsheet is in text format since eg. 0010 will lose its zeros in number format when saving to csv."),"",'colspan=1'); + label_row(_("8. Template: payment.csv for Payment only processing. Template: deposit.csv for Deposit only processing. Template: bank.csv for Bank Statement format processing."),"",'colspan=1'); +} + +function show_table_section_important_journals() +{ + table_section_title(_("Display Help Notes: Important regarding Journals"),2); + label_row(_("1. Credit amounts must be shown with a negative amount."),"",'colspan=1'); + label_row(_("2. Tax inclusive expenses (eg.rent) or tax inclusive income (eg. sales) must include a taxtype, normally 1, if a company is registered for VAT and has set up various tax types."),"",'colspan=1'); + label_row(_("3. No bank transactions are to be processed through the journal. Use 'Deposit Processing' or 'Payment Processing' for this purpose."),"",'colspan=1'); + label_row(_("4. Ensure that your csv file date format eg. DD/MM/YYYY includes a 4 number year ie. YYYY, and it complies with your User selected date format, eg. DDMMYYYY as indicated above under tools. Do this preferably with a low-level format editor since format can be lost when converting to csv from a spreadsheet package. So check your csv with Notepad+ for a four digit year."),"",'colspan=1'); + label_row(_("5. Ensure that 'Account code' in your spreadsheet is in text format since eg. 0010 will lose its zeros in number format when saving to csv."),"",'colspan=1'); + label_row(_("6. Template: journal.csv"),"",'colspan=1'); + label_row(_("7. The correct debtors control account (eg. 1100) or creditors control account (eg. 2100) should be used with Person_type_id and Person_id. Use 'Display Tax Type, Customers, and Suppliers' to check Person_type_id and Person_id."),"",'colspan=1'); +} + +function show_table_section_notes($type) +{ + if ($type == 0) + { + show_table_section_important_journals(); + } + else + { + show_table_section_important_bank(); + } +} + + +function show_table_section_tools() +{ + table_section_title(_("Lookup: Tax Type, Customers, and Suppliers"),2); + echo "";tax_types_list_cells(_("Tax Type:"),'ttype',$_POST['ttype'],_("All Tax types"),$submit_on_change=true);echo ""; + $ttype=$_POST['ttype']; + if ($ttype==-1) $ttype=null; + echo "";label_cell(_("Taxtype id:"),'colspan=1');label_cell($ttype,'colspan=1 align=right width=20px');echo ""; + echo "";customer_list_cells(_("Customer:"),'cust',$_POST['cust'],_("All Customers"),true);echo ""; + $cust=$_POST['cust']; + echo "";label_cell(_("Person_id:"),'colspan=1');label_cell($cust,'colspan=1 align=right width=20px');echo ""; + echo "";supplier_list_cells(_("Supplier:"),'supp',$_POST['supp'],_("All Suppliers"),true);echo ""; + $supp=$_POST['supp']; + echo "";label_cell(_("Person_id:"),'colspan=1');label_cell($supp,'colspan=1 align=right width=20px');echo ""; + table_section_title(""); +} + +function show_table_section_trial_or_final() +{ + table_section_title(_("Trial-run (tick) or Final-run (no tick)"),2); + check_row(_("Trial-run before importing. Do NOT process into database."), 'trial', $_POST['trial']=!null, $submit_on_change = false,$title=""); + label_row(_("Import File:"), "",'colspan=1'); + } + +function show_table_section_bankstatement_checkbox() +{ + $bs = array( _("First amount column (col D) in csv = Payments"), + _("Second amount column (col E) in csv = Deposits"), + _("The same csv file should be used for both 'Payment' and 'Deposit' processing."), + _("When you select 'Deposit Processing' then all csv lines with an amount in the Payments column D will be ignored."), + _("When you select 'Payment Processing' then all csv lines with an amount in the Deposits column E will be ignored."), + _("Typically you will import a Bank statement in csv format from your bank and adjust it according to required columns."), + _("Bank statement = columns B,C,D,E"), + _("Add columns A (reference), and columns F,G,H,I,J,K. See template."), + _("You will need to process bank.csv twice. Once with 'Payment Processing', next with 'Deposit Processing'"), + _("Template bank.csv.")); + table_section_title(_("Bank Statement Format"),2); + checkbox_row($bs, 'stateformat', $_POST['stateformat'], false, $title = "", 'colspan=1 wrap align=left'); + return $stateformat = $_POST['stateformat']; +} +?> \ No newline at end of file diff --git a/templates/bank.csv b/templates/bank.csv new file mode 100644 index 0000000..c4c04e8 --- /dev/null +++ b/templates/bank.csv @@ -0,0 +1,5 @@ +"reference","date","memo","payment","receipt","accountcode","taxtype","dim1ref","dim2ref","person_type_id (supplier = 1, customer =2)","person_id" +1,01/01/14,"labour",100.5,,6000,0,"D1","D2",, +2,02/01/14,"cash on account",,300,1100,0,"D1","D2",2,1 +"QWE2",03/01/14,"payment for rent",200.5,,7100,1,"D1","D2",, +4,04/01/14,"sold fixed asset",,700,0010,0,"D1","D2",, diff --git a/templates/deposit.csv b/templates/deposit.csv new file mode 100644 index 0000000..79d316c --- /dev/null +++ b/templates/deposit.csv @@ -0,0 +1,3 @@ +"reference","date","description","amount","from account","taxtype","Dimension 1","Dimension 2","Person_type_id","Person_id" +"INV1",15/12/14,"Receipt from Joe on account. VAT on original invoice",345.5,1100,0,"D1","D2",2,1 +1,16/12/14,"Cash Sale including VAT",400.5,4000,1,"D1","D2",, diff --git a/templates/journal.csv b/templates/journal.csv new file mode 100644 index 0000000..a0c0cd5 --- /dev/null +++ b/templates/journal.csv @@ -0,0 +1,10 @@ +"reference","date","memo","amount","accountcode","taxtype","dim1_ref","dim2_ref","person_type_id (supplier=1 customer=2)","person_id" +"JNL1",01/12/2014,"Bad debts using account 8100",222.24,8100,0,"D1","D2",, +"JNL1",01/12/2014,"Debtors Control using account 1100",-222.24,1100,0,"D1","D2",2,2 +"JNL2",02/12/2014,"Rent outstanding",122.25,7100,1,"D1","D2",, +"JNL2",02/12/2014,"Accruals",-122.25,2109,0,"D1","D2",, +"JNL3",03/12/2014,"Prepayments",322.26,1103,0,"D1","D2",, +"JNL3",03/12/2014,"Rent Prepaid",-322.26,7100,1,"D1","D2",, +"JNL4",04/12/2014,"Prepayments",400,1103,0,"D1","D2",, +"JNL4",04/12/2014,"Prepayments",400,1103,0,"D1","D2",, +"JNL4",04/12/2014,"Rent Prepaid",-800,7100,1,"D1","D2",, diff --git a/templates/payment.csv b/templates/payment.csv new file mode 100644 index 0000000..a58cdbb --- /dev/null +++ b/templates/payment.csv @@ -0,0 +1,3 @@ +"reference","date","description","amount","from account","taxtype","Dimension 1","Dimension 2","person_type_id (1=SUPPLIER 2=CUSTOMER)","Person_id" +"REF1",15/12/14,"Rent using account 7100",330,7100,1,"D1","D2",, +"REF2",16/12/14,"Paid Creditor Control account 2100",220,2100,0,"D1","D2",1,1 -- 2.30.2