Jump to content
  • Checkout
  • Login
  • Get in touch

osCommerce

The e-commerce.

Sage Instant Accounts CSV


FWR Media

Recommended Posts

This is an initial post as I add the contrib, I will update this on success.

Link to comment
Share on other sites

Ahh well I couldn't update it so will have to post here.

 

Sage Instant Accounts CSV

 

What is it?

 

I got the Sage Instant Accounts Package when I signed up with Barclays Bank for a business account.

 

After a month I still hadn't touched it as I couldn't be bothered to input the endless data.

 

I wanted this inputting automated and that is what this contribution is about.

 

What it does:

 

Sage Instant Accounts allows 5 types of CSV (Comma Seperated Value) file uploads ..

 

1) Customer Records - Included

2) Supplier Records - Not included, Osc does not support this

3) Nominal Ledger Records - Not included, Osc does not support this

4) Audit Trail Transactions - Included

5) Stock Records - Included

 

What it doesn't do:

 

Although it will record all financial transactions and tax, it does not allow for the complete information of the sale. This means that sadly we cannot drill down into the sale to see what was bought.

 

The Benefits:

 

Massive timesaving alleviating the need for manual input of your daily sales/customers/new products, allowing (semi) automated financial accounts.

 

Installation:

 

Upload 2 files.

Upload sql creating 1 table and 1 field.

Alter 1 file admin/includes/boxes/reports.php adding 1 line.

Create 1 diectory in admin sage_csv (chmod 777)

 

That's it!

 

Operation:

 

When you click on sage_csv in admin you are given 2 options ..

 

Full Download (Can only be done once)

Adds all info from the very start to yesterday.

 

Update

Updates all info from the last Update/download to yesterday.

 

Applying to Sage Instant Accounts

 

Go to File/Import this will give you 5 upload options

Upload Stock Records

Upload Customer records

Upload Audit Trail Transactions

 

Each file will go through a test in Sage to test its validity, if you get errors they are likely to be ..

 

Customer Records - If a customer uses a character that Sage does not like e.g. ? it will need to be removed you can do this manually but I'd recommend adding to the arrays in sage_accounts_csv which replaces such characters.

 

##### VERY IMPORTANT #####

The CSV files will update the Sage database and delete previous records, DO NOT use this on a live Sage DB, test first and make a back-up of your live DB.

######################

 

##### NOTE #############

You must have Optimize tep_get_tax_rate() contrib installed

http://www.oscommerce.com/community/contributions,2417/

Edited by thunderace
Link to comment
Share on other sites

Link to comment
Share on other sites

Contribution located HERE

Hi thunderace,

 

Nice useful contribution. But I'm having errors in 'sage_accounts_csv.php' . I'm getting the following error:

 

Sage Accounts CSV

 

Choose a Full Download or an Update Below: -

 

1146 - Table 'database.sage_csv' doesn't exist

 

SELECT last_update FROM sage_csv

 

[TEP STOP]

 

 

 

* Database name removed. I have installed the MOD carefully and have created the necessary Table 'sage_csv' in the database. I dunno why I'm getting this error. Please advice. Many Thanks in advance.

Link to comment
Share on other sites

Hi thunderace,

 

Nice useful contribution. But I'm having errors in 'sage_accounts_csv.php' . I'm getting the following error:

 

Sage Accounts CSV

 

Choose a Full Download or an Update Below: -

 

1146 - Table 'database.sage_csv' doesn't exist

 

SELECT last_update FROM sage_csv

 

[TEP STOP]

* Database name removed. I have installed the MOD carefully and have created the necessary Table 'sage_csv' in the database. I dunno why I'm getting this error. Please advice. Many Thanks in advance.

 

Hi

 

The only way I can replicate this error is if I delete the table sage_csv from my testsite database.

 

The code to apply the table as given in the instructions is ..

 

DROP TABLE IF EXISTS `sage_csv`;
CREATE TABLE `sage_csv` (
 `last_update` date default '0000-00-00',
 KEY `last_update` (`last_update`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

Link to comment
Share on other sites

Hi

 

The only way I can replicate this error is if I delete the table sage_csv from my testsite database.

 

The code to apply the table as given in the instructions is ..

 

DROP TABLE IF EXISTS `sage_csv`;
CREATE TABLE `sage_csv` (
 `last_update` date default '0000-00-00',
 KEY `last_update` (`last_update`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

 

Actually I'm getting the following error because of the above SQL command:

 

Error

 

SQL query:

 

CREATE TABLE `sage_csv` (

`last_update` date default '0000-00-00',

KEY `last_update` ( `last_update` )

) ENGINE = MYISAM DEFAULT CHARSET = latin1;

 

MySQL said: Documentation

#1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'ENGINE=MyISAM DEFAULT CHARSET=latin1' at line 1

 

---------------------------------------------------------------------------------------------

 

Hence I modified the SQL command to this:

 

Error

 

SQL query:

 

CREATE TABLE `sage_csv` (

`last_update` date default '0000-00-00',

KEY `last_update` ( `last_update` )

);

 

-------------------------------------------------------

 

This creats the sage_csv table but then I get the error as mentioned in my first post..

Link to comment
Share on other sites

Actually I'm getting the following error because of the above SQL command:

 

Error

 

SQL query:

 

CREATE TABLE `sage_csv` (

`last_update` date default '0000-00-00',

KEY `last_update` ( `last_update` )

) ENGINE = MYISAM DEFAULT CHARSET = latin1;

 

MySQL said: Documentation

#1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'ENGINE=MyISAM DEFAULT CHARSET=latin1' at line 1

 

---------------------------------------------------------------------------------------------

 

Hence I modified the SQL command to this:

 

Error

 

SQL query:

 

CREATE TABLE `sage_csv` (

`last_update` date default '0000-00-00',

KEY `last_update` ( `last_update` )

);

 

-------------------------------------------------------

 

This creats the sage_csv table but then I get the error as mentioned in my first post..

 

 

I can't replicate this at all, what version of SQL are you running? Do you have phpMyAdmin?

Link to comment
Share on other sites

I can't replicate this at all, what version of SQL are you running? Do you have phpMyAdmin?

 

Try saving the below code as a php file in the \admin directory, call it something like add_sage.php, then run it in your browser.

 

<?php
 require('includes/application_top.php');
$drop_sage = 'DROP TABLE IF EXISTS `sage_csv`';
tep_db_query($drop_sage);
echo 'sage_csv table dropped<br />';
$add_sage = 'CREATE TABLE sage_csv( '.
	 'last_update date, '.
	 'PRIMARY KEY(last_update))';
if (tep_db_query($add_sage)) echo 'sage_csv table applied<br />';
else echo 'sage_csv table failed!<br />';


 require(DIR_WS_INCLUDES . 'application_bottom.php');
?>

 

Ensure to delete the php file after use.

Edited by thunderace
Link to comment
Share on other sites

Try saving the below code as a php file in the \admin directory, call it something like add_sage.php, then run it in your browser.

 

<?php
 require('includes/application_top.php');
$drop_sage = 'DROP TABLE IF EXISTS `sage_csv`';
tep_db_query($drop_sage);
echo 'sage_csv table dropped<br />';
$add_sage = 'CREATE TABLE sage_csv( '.
	 'last_update date, '.
	 'PRIMARY KEY(last_update))';
if (tep_db_query($add_sage)) echo 'sage_csv table applied<br />';
else echo 'sage_csv table failed!<br />';


 require(DIR_WS_INCLUDES . 'application_bottom.php');
?>

 

Ensure to delete the php file after use.

 

BINGO!! That's Brilliant mate..It worked..Many Thanks.. :thumbsup:

Link to comment
Share on other sites

  • 3 months later...

Hi,

 

Thanks for this contrib.

 

I installed it as a possible answer to my prayers as I need to transfer my data from my shop (lots of it) into Sage line 50 in order to operate a till system and keep my accountant happy.

 

The install went fine. But when I did the Full update action, it says I only had 8 stock records. Does stock mean products as I have around 800 products that I want to transfer into Sage?

 

Secondly, does anyone know if this is compatable with Sage Line 50 v11 rather than any lesser Sage package??

 

Thanks,

Link to comment
Share on other sites

Hi,

 

Thanks for this contrib.

 

I installed it as a possible answer to my prayers as I need to transfer my data from my shop (lots of it) into Sage line 50 in order to operate a till system and keep my accountant happy.

 

The install went fine. But when I did the Full update action, it says I only had 8 stock records. Does stock mean products as I have around 800 products that I want to transfer into Sage?

 

Secondly, does anyone know if this is compatable with Sage Line 50 v11 rather than any lesser Sage package??

 

Thanks,

 

Only Instant accounts as it's all I have.

Link to comment
Share on other sites

  • 1 year later...
  • 8 months later...

Hi Robert, I do have one slight problem - everything works fine except that the products description doesn't appear in the stock records csv file (all other columns are populated correctly). Would you be able to give advice on that?

Link to comment
Share on other sites

  • 1 year later...
  • 5 months later...

I was wondering if it is possible to get headers above the customer loop? If it is possible then it is possible to download and import the file direct into sage.

 

I was wondering where i would have to place the headings:

 

 

Do I have to tweak it a little bit around: ???

 

//print_r($customers); //Test print array

tep_db_free_result($new_customers_result); //Housekeeping

//End do the queries

$cust_count = count($customers);

if (!empty($customers)) { //If no new customers have signed up since the last update then skip writing a CSV

for ($i=0; $i < $cust_count; $i++) { //Loop through all customers that have purchased

$handle = fopen(DIR_FS_ADMIN . 'sage_csv/customers_' . $filename . $date_updated . '.CSV', 'a');

fwrite($handle,

$customers[$i]['customers_id'] . ","

$customers[$i]['customers_city'] . "," .

$customers[$i]['customers_state'] . "," .

Link to comment
Share on other sites

I was wondering if it is possible to get headers above the customer loop? If it is possible then it is possible to download and import the file direct into sage.

 

I was wondering where i would have to place the headings:

 

 

Do I have to tweak it a little bit around: ???

 

//print_r($customers); //Test print array

tep_db_free_result($new_customers_result); //Housekeeping

//End do the queries

$cust_count = count($customers);

if (!empty($customers)) { //If no new customers have signed up since the last update then skip writing a CSV

for ($i=0; $i < $cust_count; $i++) { //Loop through all customers that have purchased

$handle = fopen(DIR_FS_ADMIN . 'sage_csv/customers_' . $filename . $date_updated . '.CSV', 'a');

fwrite($handle,

$customers[$i]['customers_id'] . ","

$customers[$i]['customers_city'] . "," .

$customers[$i]['customers_state'] . "," .

 

This contribution is really old and was really only ever half done .. there is so little you can do with the base Sage that it is hardly worth bothering.

 

I no longer use Sage so have no means with which to test this or develop it further.

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Unfortunately, your content contains terms that we do not allow. Please edit your content to remove the highlighted words below.
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...