Sage Line 50 Zen export module

[MikeyG]

Calljj

Have you been able to upload? can you post the script in this thread if easier?

Thanks and regards

MG

[Calljj]

Hi Apologies for the delay, its always a pain when you overwrite something without realising, so had to start from scratch and been very busy.

I've just uploaded now, so should be available soon, else here is the sql below that you can just copy/paste for the install

Kind regards

James

www.janetsystems.com

CREATE TABLE sage_update
(last_update datetime);

Insert into sage_update Values ('0000-00-00 00:00:00');

SET @t7=0;
SELECT (@t7:=configuration_group_id) as t7
FROM configuration_group
WHERE configuration_group_title= 'SAGE';
DELETE FROM configuration WHERE configuration_group_id = @t7;
DELETE FROM configuration_group WHERE configuration_group_id = @t7;

INSERT INTO configuration_group VALUES (NULL, 'Sage Configuration', 'Set Sage Options', '1', '1');
UPDATE configuration_group SET sort_order = last_insert_id() WHERE configuration_group_id = last_insert_id();

SET @t7=0;
SELECT (@t7:=configuration_group_id) as t7 
FROM configuration_group
WHERE configuration_group_title= 'Sage Configuration';

INSERT INTO configuration (configuration_id, configuration_title, configuration_key, configuration_value, configuration_description, configuration_group_id, sort_order, date_added, use_function, set_function) VALUES
(NULL, 'Output File Name', 'SAGE_OUTPUT_FILENAME', 'sage_stock_transaction.csv', 'Set the name of your sage stock output file', @t7, 1, NOW(), NULL, NULL),
(NULL, 'Output File Name', 'SAGE_OUTPUT_FILENAME2', 'sage_sales_transaction.csv', 'Set the name of your sage sales output file', @t7, 2, NOW(), NULL, NULL),
(NULL, 'Output Directory', 'SAGE_DIRECTORY', 'sage/', 'Set the name of your file output directory', @t7, 3, NOW(), NULL, NULL),
(NULL, 'Start Date', 'SAGE_STARTSDATE','2007-01-01', 'Set the start date of your output file - set to null to use last update, or date format yyyy-mm-dd', @t7, 4, NOW(), NULL, NULL),
(NULL, 'End Date', 'SAGE_ENDSDATE', 'null', 'Set the end date of your output file, or set to null for now - date format yyyy-mm-dd', @t7, 5, NOW(), NULL, NULL),
(NULL, 'Order Status', 'SAGE_STATUS',' 4', 'Set the Complete Status Number of your Order Processing to process all below this level', @t7, 6, NOW(), NULL, NULL),
(NULL, 'Transaction Code', 'SAGE_TX','GO', 'Stock Tranascation Code for Sage', @t7, 7, NOW(), NULL, NULL),
(NULL, 'Sales Code', 'SAGE_TX2','CR', 'Sales Tranascation Code for Sage', @t7, 8, NOW(), NULL, NULL),
(NULL, 'Paypal/Credit Card Nominal Code', 'SAGE_PAYPAL', '1205', 'Set the Sage Nominal code for Paypal/Credit Cards e.t.c', @t7, 9, NOW(), NULL, NULL),
(NULL, 'Bank/Cheque Nominal Code', 'SAGE_CHEQ', '1200', 'Set the Sage Nominal code for Cheques/Bank transfers e.t.c', @t7, 10, NOW(), NULL, NULL);

[MikeyG]

Calljj,

Thank you for the script have run and certainly got the options menus etc.

Having tried to run the sage export I get the following error

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in .....\Websites\sage.php on line 174

Is this a bug or have I not set something up correctly.

Thanks

MG

[MikeyG]

Calljj,

I might have sorted this out. The need for a table prefix in the SQL statment in the line above 174.

Please confirm this could be cause.

Regards

MG

[MikeyG]

Calljj

Sorry another question.

I do not seem to be getting any stock quantity value exported with the stock file?

Thanks

MG

[Calljj]

Calljj,
I might have sorted this out. The need for a table prefix in the SQL statment in the line above 174.
Please confirm this could be cause.
Regards
MG

Hi,

I've coded

$query = mysql_query("SELECT * FROM products WHERE products_id =$prodid");

You could change to

$query = mysql_query("SELECT * FROM TABLE_PRODUCTS WHERE products_id =$prodid");

[Calljj]

Calljj
I do not seem to be getting any stock quantity value exported with the stock
MG

Is this the stock in the transactions module, or the stock module?
No problem in the transactions module, its dealt with on line 210.

In the stock module (to download just stock records for when starting up sage and automatically entereing your product IDs and descriptions) then Sage doesn't allow for the import of stock quantity.

[MikeyG]

Calljj,

Thanks for your reply. It is the stock module and I understand now having had a demo this morning from sage that yes you would not take stock in here.

However I am wonder if you could just explain / expand on the transaction import please:

I am getting an error of field 2: Invalid Bank Account

I clearly don't understand the account set up and account types in sage yet. Is it possible just to explain how the 2 files work to gether in the sage export (sales) module.

I have obviously missed something somwhere in my set up.

Thanks once again

MG

[MikeyG]

Calljj,

I have just tried to import the stock transaction but get a fail on pass 2 due to insufficinet stock. As a business I can accept back orders so how might I get round this one.


Apologies if again I have missed the point here, but a good testing ground I hope from a complete novice trying to use it.

Thanks

MG

[Calljj]

Calljj,

I am getting an error of field 2: Invalid Bank Account

MG

Okay, this is where you need to set up your Nominal Codes in Sage.
The standard code in Sage for a bank account is 1200, then 1205 for credit cards.

Once you have set these in Sage (i think they are default numbers anyhow) then you set them to be the same in the configuration screen on the zen module, where i think i have also set them to be 1200 and 1205, but they can be changed to suit your need.