It's much more fun, when you can discuss your problems with others...
You are not logged in. Please login or register.
FrontAccounting forum → Posts by 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'
Sounds good. Let me know if you'd like help testing when you're ready.
I am working on osCommerce specific code right now.
It may be 'easy' to convert to read your data source... if I write clean enough code 
Yes it is a bit different, but once you have a sales order in the system a few simple clicks and it is ready to ship...
Those clicks may be easily automated...
We'll see how helpful my code is, once I am done
I take things one small step at a time...
at the rate things are going I expect to have something of interest in a few weeks.
tom
You can also follow a member of the community, tom. He is planning to do this with osCommerse. Eventually he sees this topic, otherwise ask him directly.
/Joe
Yikes!
hat I am working on is taking osCommerce Orders (I guess it does look a lot like an Invoice) and importing them into a FA Sales Order.
Once it is a SO then FA can take over...
What online shopping system are you using?
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
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
Here's a FA DB question.
Does the debtors_master.name need to be unique?
osComerce uses the email address as being unique.
Tom
Base for auto price calculations:
That was it, thanks
It would really be great if there was a choice at install time.
Since the superuser is only part of the First company created, it would be nice if there was a Create Tables option that did not include any demo data.
Or should I say no transactions.
A number of things can not be deleted once there are some transactions, that makes it hard to start a new company.
tom
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
I am running ver 2.1.1 and in sales order entry the Prices are not updating when I select an item or change price lists
I do have JavaScript enabled
could it be a config setting?
I do have 'Show Item code' enabled
tom
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
Ok, upon looking at things closer, it appears that Item Codes (or Foreign Item Codes) are used to translate a UPC/Barcode (aka Foreign code) into a stock item.
I see the quantity is used as a case quantity of the Stock Item we work with.
Are entries in the item_codes table required?
Why is item_code == stock_id in item_codes?
I expected item_code to be a foreign id, not a stock id.
tom
I see stock_matster and item_codes
Why do they both have category_id?
Since item_codes.id is the key what is the meaning of a specific stock_id appearing more than once? Will they have different category_id's?
Also there is a Quantity field, isn't the quantity of a given item determined by looking it up in Stock_Moves as menti0ned above?
I would like to update items_import, but will need to understand what needs to be done in very simple terms.
I may be able to take initial stock quantities at the same time, but I think that is a secondary step at th moment...
tom
I have an updated Customer_Import... how do I upload to th site?
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
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
I agree.
Thanks for helping me understand it better.
tom
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
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
Modifying addresses in customer/branch sales order delivery address unless you reedit SO.
In fact algorithm of selection of delivery address on new SO is quite logical:
- select branch mailing address if not empty
else
- select branch billing adress if not empty
else
- select company address
That's all.
That makes sense. but since osCommerce does much of the same logic, I will have all three addresses (generally the same) and it is far simpler
logic to fill them in all the time... (Customer/Company Address, Billing Address, Shipping Address)
tom wrote:I am just looking for any bad things that could happen by doing things this way...
All should work right. The only problem can be changed branch address between sales order is received and moment of delivery. In this case all not delivered sales orders should be updated.
Yes and I take that to be bad, so it makes sense to only allow FA to make changes to SO or Customer Branches, etc
I guess you mean customers not users? You can use 'Retail' customer with many branches, or you can create unique customer record for every your client. The first option cannot be used for multiply companies because GST number is stored in customer, not in branch record. For retail customers GST number is obsolete.
Yes I did mean Customers.
One thing I did notice, the Customer's Address does not have a Tax Group associated with it, so I will need at least one Branch for each customer.
No big deal, just my observation.
In my case I do not sell my products for re-sale, so every customer is a Retail Customer.
But I do want to make any code I write general enough so others can use it too...
tom
Ok maybe I misspoke...
I just did some more testing and I see that if I change the address in the branch it does not effect the Sales Order.
So what effect will modifying the Branch have on any current or past sales orders (or invoices or...)?
I saw the "Retail Customers" in the demo an have considered putting all the web orders under a single FA customer and using the branches for each individual osC customer address.
And if the customer changed their address, I was going to update their FA Customer Branch accordingly.
I am just looking for any bad things that could happen by doing things this way...
If this will work, then I just need to have the osC 'order process' generate a sales order and then the normal FA interface can be used for the rest of the process.
I am very content to continue to use osC to interface with the customer because that works well. I need FA to track the *rest* of the business!
[edit]
Upon thinking about it more...
I can not think of a good reason to not create a FA user for each osC user
(except maybe lazyness LOL)
At that point I might as well create Branches on the fly as Ship To addresses
change... which it typically rare with my customer base
It then may make the most sense to do everything from the Order Process function in osCommerce.
That may keep things simpler in the long run...
tom
ps. let me know if there is a better place to discuss this kind of thing
I am starting to examine how to interface between osCommerce and FA.
I noticed that you can not delete a Customer Branch that has 'customer transactions' or 'sales orders' in the database.
I also see that there are no restrictions on how you can edit the branch
I think that is may be valid to edit most anything while the order is being handled,
but once the sales order is closed/delivered/etc it should be copied for future preservation.
What would it mean if I took a sales order that shipped to branch in the UK and then the following week that address book entry (branch) was edited to be in New York City?
Would the old sales order now display NYC with taxes collected to UK?
In osCommerce that is what we did with everything, including product descriptions, prices, taxes, etc.
sales_orders_details appears to not depend on other tables, but sales_orders
does refer to a few others.
tom
Try SETUP->Display Setup
Prices/Amounts
tom
Ok Thanks!
I will likely do the manufacturing process and then when we get PO Units recalc I will be able to drop that step.
Now on to filling orders!
I am sure I will have more questions... but as always I try to ask questions verbose enough to provide help to other people
Tom
FrontAccounting forum → Posts by tom
Powered by PunBB, supported by Informer Technologies, Inc.
Currently installed 4 official extensions. Copyright © 2003–2009 PunBB.