It's much more fun, when you can discuss your problems with others...
You are not logged in. Please login or register.
FrontAccounting forum → Modules Add-on's → osCommerce
This seems a more logical place to continue this topic...
What is the best way to have osCommerce and FA interface?
I am inclined to want to have them on the same server with different databases.
Is there any advantage to having them in the same database? (locking for updates?)
I would prefer not having them on different servers, since the problems of what to do when the other is down...
Tom
I suggest you create a module in FA. Look at some of the existing modules how they are setup. You can download them in the Download - Modules section. This module could belong to the Sales tab. The menu-choice could be 'Import SO from osCommerce' or something like that.
Using our API you could then write a script page and retrieve the SO's from osCommerce easily. Of course you will have to know the table structure of osCommerce and FA for doing this.
Because SO's don't create any GL entries, it is the best way to import the SO and from here create deliveries and invoices in FA.
I guess this sounds like a great task, tom.
If you need help doing this, please ask for it. There are several skilled developers here in the community.
/Joe
I was trying to automate that part, but since there will be other manual steps, I think you are right. I will look into it.
I am sure I will ask for help...
Are there any other modules that create SO's?
I will browse the modules
tom
Here is the osCommerce SQL to dump the customer list in the import format
SELECT c.customers_id, CONCAT(c.customers_firstname, ' ', c.customers_lastname) , b.entry_street_address, b.entry_suburb, concat(b.entry_city, ', ', if (b.entry_zone_id = 0, b.entry_state, z.zone_code)), concat(' ', b.entry_postcode), '', concat(' ', c.customers_telephone), '', c.customers_email_address, '', '', 'USD', if (b.entry_zone_id = 0, '', z.zone_name) FROM customers c left join `customers_info` i on c.customers_id = i.customers_info_id left join address_book b on c.customers_default_address_id = b.address_book_id left join zones z on zone_id = b.entry_zone_id where `customers_info_date_of_last_logon` > '2007-1-1' or `customers_info_date_account_created` > '2007-1-1'
I see that FA requires names to be unique, have to watch that during import.
osCommerce used the email address as the key.
I have duplicate names, mostly from people changing email addresses, but I do have 2 customers with the same name! - I will have to edit them to make then unique...
I also added the Tax Group on the SQL, I am going to modify the import script to use it if it is there.
Tom
Here is the osCommerce SQL to dump items.
I may not be able to use this directly, because I will need to figure out my units
SELECT p.products_model, pd.products_name, cd.categories_name, 'each', 'B', 'USD', p.products_price
FROM
products p left join products_description pd on p.products_id =
pd.products_id and pd.language_id = 1 left join products_to_categories pc
on p.products_id =
pc.products_id left join categories_description cd on pc.categories_id =
cd.categories_id and cd.language_id = 1 where p.products_status = 1
I also have a number of sales kits I need to remove and add by hand
tom
I have an updated Customer_Import... how do I upload to th site?
you can send it me at joe (dot) hunt (dot) consulting (at) gmail (dot) com. Sorry for the cryptographic email, but necessary due to spam protection. I will then have a look at it and publish it.
/Joe
Is there a field in the database that indicates the release version?
That will be helpful to add-on modules to see if they work or not...
tom
Hello tom,
The release (version) number is put in a global variable in config.php
$version = "2.1.1";Global variables are mainly put in config.php, due to that the databases are company based.
/Joe
Yes I have seen that, but since each upgrade may (will likely?) require db changes it would be a good safeguard for cases where a company was overlooked.
$version says what version the code is and a db entry tells you what version the db has been updated to.
They could be the same value so that when $version != $row['version'] then we need to warn user that the db may need to be upgraded.
tom
It seems like a good idea, tom. We will consider this for the 2.2 release.
/Joe
Here's a FA DB question.
Does the debtors_master.name need to be unique?
osComerce uses the email address as being unique.
Tom
Hello tom,
Yes, according to the table structure it must be unique. While you never see the customer number, it would be difficult to separate them if not.
BTW. tom, I guess you had a proposal for changing the install script to handle empty chart of accounts, using en_US-new.sql instead of the demo one.
I didn't like the idea in the form you suggested, but we could do it your way with a GET parameter, f.i. install/?accounts=new.
What do you say to that?
/Joe
Hello tom,
Yes, according to the table structure it must be unique. While you never see the customer number, it would be difficult to separate them if not.
/Joe
My problem is that osCommerce uses the Email Address as unique, not a person's name.
Could you consider the problems associated with having the name not Unique?
I was considering changing 'customer_list_row' to include the email address in the pull-down text to aid in choosing WHICH 'John Doe' you wanted.
change line 403 of includes/ui/ui_lists.inc
$sql = "SELECT debtor_no, IF(email = NULL, name, CONCAT(name, ' (', email, ')')), curr_code FROM ".TB_PREF."debtors_master ";
That should cover the majority of the places you might need to pick between two people with the same name.
The other alternative would be to use a nickname for one of them, but if it is not a nickname they actually USE, it would be confusing.
I'd suggest making the email address unique, but that may cause bigger problems for cases where email is not used... (if any)
Yes I know I could make a hack in my copy of the code, but I want to make sure any modules I provide can be used easily with out
too many (any?) additional changes to the base code.
Maybe there is another solution that you can think of that fits better within FA...
I am just looking for the best long term solutions...
tom
Hello tom,
BTW. tom, I guess you had a proposal for changing the install script to handle empty chart of accounts, using en_US-new.sql instead of the demo one.
I didn't like the idea in the form you suggested, but we could do it your way with a GET parameter, f.i. install/?accounts=new.
What do you say to that?/Joe
How about an EXPERT mode button that adds a GET parameter, they we would consider any number of additional options for advanced installers?
That could be of some use to the other problem I am looking at...
tom
I ported the Customer Import into the osCommerce module, but when I started looking at the Item Import it occurred to me that item import has too many special cases to do it automatically.
On to Order Import!
First I'll make a Sales Order and I may have an option to act like 'Direct Invoice'
Well I have imported my first Sales Order!
Now I need to clean up the error checking and use proper FA notification messages
I really think I will try going the Direct Invoice route, because I think that is the only way I will be able to verify that osCommerce and FA computed the same taxes!
Can not collect one amount of taxes and report another!
The government does not like that!
tom
I have cleaned things up a bit.
I also expanded the item_import to support Sales Kits and Foreign Item Codes.
I am going to put together the modules and get them uploaded.
I stop at Sales Orders at the moment.
Sounds promissing Tom 
. What was the problem with taxes you have noticed above?
Janusz
I have not seen any problems, I am just anticipating real life.
Since osCommerce computes Taxes and displays them to the customer and then
later FA has to re-compute taxes based on what is actually delivered.
I am worried about telling the customer the taxes are $x and then FA computing them to be $y
Based on the GL accounts we will send $y to the government, people tend to get upset if we collect one total and pay a different total.
(I think you can get fined if you Under pay taxes... over paying is usually not a problem!)
tom
This is strange.
In my order import I use add_to_order() (sales/includes/ui/sales_order_ui.inc)
If I import an order with a Description that has a " in it I end up with
16" Plate Glass Blank
And if I enter the sales order by hand I get
16" Plate Glass Blank
Here is my code
include($path_to_root . "/includes/session.inc");
include_once($path_to_root . "/includes/ui.inc");
include_once($path_to_root . "/includes/data_checks.inc");
include_once($path_to_root . "/sales/includes/db/branches_db.inc");
include_once($path_to_root . "/sales/includes/db/customers_db.inc");
include_once($path_to_root . "/sales/includes/db/sales_order_db.inc");
include_once($path_to_root . "/sales/includes/cart_class.inc");
include_once($path_to_root . "/sales/includes/ui/sales_order_ui.inc");
$cart = new Cart(30); // New Sales Order
$cart->customer_id = $customer['debtor_no'];
$cart->customer_currency = $customer['curr_code'];
$cart->Branch = $branch['branch_code'];
$cart->cust_ref = "osC Order # $oID";
$cart->Comments = $comments;
$cart->document_date = Today();
// $_POST['OrderDate'] = $cart->document_date;
$cart->sales_type = $customer['sales_type'];
$cart->ship_via = $branch['default_ship_via'];
$cart->deliver_to = $branch['br_name'];
$cart->delivery_address = $branch['br_address'];
$cart->phone = $branch['phone'];
$cart->email = $branch['email'];
$cart->freight_cost = $order_total['value'];
$cart->Location = $branch['default_location'];
$cart->due_date = Today();
$sql = "SELECT * FROM orders_products WHERE orders_id = $oID";
$result = mysql_query($sql, $osc);
$lines = array();
while ($prod = mysql_fetch_assoc($result)) {
add_to_order($cart, $prod['products_model'], $prod['products_quantity'], $prod['products_price'], $customer['pymt_discount']);
}
mysql_free_result($result);
// print_r($cart);
$order_no = add_sales_order($cart);
display_notification("Added Order Number $order_no");
What is funny, When I call add_to_order I do not pass the description, it gets pulled from the FA db.
confusing...
tom
Before storing into database the text fields are escaped in db_escape() function. This is to avoid SQL injection problems. But if the db_escape() is used twice you end with broken text because '"' is escaped to &er;"', so before storing data it should be htmlspecialchars_decode()'d.
Janusz
Yes we agree.
But!
The string that has the problem is the DESCRIPTION which I am not passing to add_to_order, so it is NULL was add_to_order calls $order->add_to_cart and then the description is gotten from get_item()
I'll run an sql_trace and see...
add_to_order calls $order->add_to_cart, calls line_details wth a NULL description so get_item is called and it uses that description, at that point the description is correct.
still tracing...
btw: add_to_cart and line_details are from sales/includes/cart_class.inc
----
I think the problem is that get_item fetches item details from the database and does not htmlspecialchars_decode() the data.
Maybe the caller should do it, but since get_item is a fairly generic utility function it should return plain decoded data
I am not sure the other impacts. - I did not see many other calls.
They were mostly used in areas that would be used for Display, not feed back to the database.
I will change it locally in line_details (since it knows it will feed it back to the db) and if that is the wrong change
i'll get the right one from CVS
tom
No, Tom. We have decided to use encoded strings everywhere to prevent injection problems, so it will not be changed. Please follow this convention - this is thoroughly tested concept. The strings have to be decoded only when they are got from database and are to be written again by any db layer function (I mean the functions in */includes/db/* files).
You will find an example of rewriting documents stored in database in function write in items_cart.php (html_entity_decode calls).
Janusz
FrontAccounting forum → Modules Add-on's → osCommerce
Powered by PunBB, supported by Informer Technologies, Inc.
Currently installed 4 official extensions. Copyright © 2003–2009 PunBB.