We are working as agents and consultants in Germany, Austria and Switzerland. Our primary clients are manufacturers of design lighting (lamps) who are instructing us to take care of their national markets and interests in these countries. Our agents on the road are fetching the orders and complaints and other information's, clients are ordering by fax or phone and we want to have it all in one platform.
We are working with hundreds of retailers (our second client group), but not we but the manufacturers are invoicing the delivered products. That is how it is if it is an agent's structure.
The platform should be used for all orders, all complaints and to offer important information's and functionalities to the different clients. If the client has put a product into the shopping cart, also his agent should be able to see the content and to add products into it. The thing is that the delivery only is free if the clients are ordering for a certain amount. Below these amounts transport costs or even administration fees will be charged. Therefore the client and their agent should use the shopping cart to collect the single orders to reach the different limits. For each sale we receive a certain commission which is different to the one we pay to the agents. The system should produce commission-lists to prepare the monthly payments to agents and the creating of the commission-invoice for the manufacturer.
Till now we were working with Microsoft Office Pro using several custom-made tools to do our work. But to be more flexible we want to change the whole structure to a web based one. The base of the platform should be oscommerce. Several changes have to be done:
The client's database has to be extended by maximum 50 fields. The content of these fields will be text, numbers, radio buttons and other client specific information's.
The entry for the clients and for the agents should be done with the standard login. Some functions should be included. The first field should be a dropdown field which decides whether the user is a client, an agent or the chief(that means ?us?). The client only can see his own information's, the agent should see selectable information's of clients only out of his area which is defined by post codes (another solution would be 1:n relationship). And the chief should have access to all clients of all areas. If the user is an agent we will need a little table or array to include the postcodes of the area he will be responsible for. The form of the German postcodes for example looks like this: 33000-33999, 40000-48999, 50000-53999, 57000-59999. A tool can decide which new entries will be in a certain agent's list.
The second field should switch between the three countries Germany, Austria and Switzerland, which have different percentages of VAT and different transport costs. This is important to us because in our actual shop we are showing the public prices excluding and below that including the national tax (VAT)(only logged in and registered clients can see these prices). Also the transport costs are different because Switzerland is not part of the EU and they can not use the tax identification number (VAT Number).
The majority of the fields will be the same than in OUTLOOK, but we have to add fields for payment conditions, buying conditions, transport conditions and payment terms like INVOIVE, DIRECT DEBITING or ADVANCED PAYMENT. Also we safe the client's address, the invoicing address and the delivery address. If they are always the same the store address should be the default address.
Because information's about the agent's and our own commissions are sensible the clients should not see these sensible information's.
In the standard configuration a person can enter the store just by filling out all the necessary fields. After one has received the password one can order. This should be changed like it is now in our actual store. In the moment an interested person or company can enter their data, but only the admin can decide whether they can buy or not. This is done by the oscommerce traffic light. Now we want to have this possibility also for the agents. Also they should have the right to enter new clients and activate them. As soon as someone enters his data the chief should receive an email with an attached vcf file to integrate the new file into the pst of our OUTLOOK database.
We are offering several different variations of payment condition and payment terms. As payment terms we offer advanced payment, direct debiting and invoicing. Matching with these we offer variable payment conditions: 3% discount for fast payment during the first 14 days after invoice date and 30 days net, 5% special discount in case of direct debiting, 0-5% for advanced payment. In bigger projects we expect a 50% payment with the order and the last 50% during 8 days after the delivery. So we need minimum two fields, the percentage and the definition of the payment terms in a table. IMPORTANT: The payment conditions are influencing the commissions of the agent and of the chief.
Here we need 2-3 fields. If the registered client orders an article the price will be reduced by his discounts (0-50%). Bigger clients have two discounts (50%+0-8%). The first and second discount should not be edited by the agent or the client himself, but they should see these information's. But the agent needs the possibility to enter an extra discount for a specific order during he is ordering. For example if a registered client is giving a bigger order for his magazine or for his showroom. This third discount should only work for this order once. For the following orders the basic conditions should be valid. Only the agent and the chief should be able to enter these special conditions. The best solution would be to have the basic discounts in the client's information's and to offer extra conditions in the order area. IMPORTANT: The buying conditions are influencing the commissions of the agent and of the chief.
Free delivery in all three countries for orders with a value of more than 600.-? net, what means after the public price is already reduced by the client specific discount(s). Below this the manufacturer is charging 25.-? if no floor standing lamp (one of the categories) is included. If a floor standing lamp is included the transport costs are raising to 45.-?. If the total net is below 200.-? an extra service fee of 7.-? will be charged (like in the MINIMUM ORDER contribution). Spare parts (own category) will be send lump for 20.-?. This is different in Switzerland. Here we have to charge 50.-? for each kind of transport, but also here the delivery will be free of transport costs if the value of the order is higher than 600.-? net. The whole solution could be a table or array (20/25/45 50/50/50). Because we have bigger clients who pick up their order by themselves we need the possibility to set the service fee and the transport costs to zero for specific orders.
Also this database needs more fields which only should be shown to the visitor if they contain information. The majority of these field will be dropdown fields. Three downloads (technical drawing, high resolution photo or user manual) should be possible for each article. Later we want to add a pdf creator to create datasheets on the fly. Also we are thinking of to create a barcode on the screen based on the 13 digit EAN code.
Some product groups like spare parts or catalogue material should not be shown in NEW PRODUCTS or EXPECTED PRODUCTS. Also they should not be taken into consideration if it is about agent's commissions. We will not pay commissions for spare parts. All these articles are in specific categories or their article code starts with 9XXX and has four digits. But to prepare the use of this store also with other collections we should solve this exception on base of the category.
Here we need a change which we already included in our actual oscommerce shop. In the standard oscommerce installation an article which is ordered twice in one order just will raise the number of ordered articles of the first ordered one. This is something we changed to offer our clients to order one and the same article two or more times for different references/customers. We included a field in which the client can note his own references for each ordered position, which makes it much easier to control whether each customer's order is done.
In the last step of the order the agent and the chief should have the possibility to use special conditions (please have a look to buying conditions). These special conditions should only work for this specific order once and should be marked in red or something equal.
If the order is done the system sends four order emails (client, agent, manufacturer, chief).
The sort of the ordered products should be that way that the last ordered one is on top. This prevents the scrolling in bigger orders.
Sometimes a client has ordered some products and wants to add one or more products some days later to avoid additional transport costs. This is a mean situation, because in this moment we can not be sure whether the first order is still in the manufacturer's magazine or if it was already picked up by the forwarding agency. This information we cannot get via internet yet, we have to call the manufacturer of we have to send an email. So we have to distinguish orders from the beginning whether it is a new order or an add-order of the specific client who the agent or chief has chosen from a list. If it is an add-order the client, the agent or the chief has to choose to which order this add-order should be added (normally it will be the last order). If this order is still in stock it is no problem at all, it will be added and send. But if the first delivery is already gone, we have to decide what to do with it.
There two possibilities: The first would be to send the add-order like a normal order. This might make it necessary to add the service fee and the transport charges depending to the net value of this former add-order. And somehow also the turnover and commission lists have to become updated.
The second would be to return this order to the client/agent, to eliminate it from the order-, turnover- and commission lists and to move it back into the shopping cart to offer the possibility to keep on waiting till one adds more products in the following days. In this case the order could also cancelled completely.
The best will be include a dropdown field after the client, agent or chief has selected ?add-order?. This dropdown field should include the information's how to actwith the add-order if the first order has already left the manufacturer's stock.
Also the system could send an email to the manufacturer to ask whether he already has sent the first order. If the manufacturer replies in a certain way the system could go on acting.
Cancellation of an order
The same email could help us to solve the problem of the cancellation of an order. Also in this case we need the manufacturer's reply to go on. If the delivery is still in stock, it will be no problem to cancel this order. Then we have to book back or to add a negative order to the depending lists like turnover and commissions. Also each party (client, agent, chief) has to get the information that the cancellation was done or not possible anymore.
Complaint of a delivered product
We also want to use the products database to work on the complaints of our clients. If a product reaches our client in damaged condition our first questions is whether the box arrived damaged, whether it is a transport damage. Or is it complaint caused by the production in the factory?
We will ask for the products code, the number of the order and of the order confirmation, the date and time of delivery and a description of the damage. It would be possible to offer a PDF drawing of the product to ask the client which parts of the product are damaged, what would make it much easier to solve the problem. We also want to know whether the client also bought the product he is complaining.
Each agent receives a certain commission for each order which is payable after the client has done his payment. If he sells ?good? which means he could sell with lower discounts his commission will be higher. If he is using higher discount, higher discounts for special orders, higher discounts for fast payment or a longer payment time his commission will be reduced by 50% of each additional condition he gave. This can be solved by a nice table. The system should create exportable lists we can use for the payments.
After each month the manufacturer informs us which client's invoice were paid and now should be commissioned. We should have to mark the single order to move the ones we paid commissions for out of this list. Only the ones which are still in procedure or the ones which are not paid by the client yet should stay in this list.
The client should see his own information's in the typical area of oscommerce. For the agents this area has to be adapted and should show all orders and the commissions including all important information's like client's name, client's code, date, order amount, discounts, commission percentage and commission amount. He should see a total and should sort by months. He also should see whether his turnover is higher or lower than the year before and the target which can be set in the admin area.
The information's for the chief could be shown in the admin area. In our actual store we also included the possibility to change all the texts (start page, emails and so on) in this area.
The content of the shopping carts would give us the possibility to use these information's for an order and production forecast. We want to have the possibility to see all filled shopping carts with the article code and the number of ordered products and even more.
We are searching for a professional programmer, who understands our business structure, is able to adapt it to our web platform and knows about the responsibility and importance of his clean and stabile programming. He should create a clear and intelligent database structure, which offers all data's for future solutions. We prefer to cooperate with our partners in long-term relationships with trust. The job can be done step by step and each upload has to be tested back and forth. We expect an oscommerce professional!
The actual databases are already filled with information's of articles, clients and done orders. We need these information's transferred into the new databases to see how turnovers are developing over the weeks, months and periods.
Please have also a look at the PDF file we included. Please be patient while downloading, the size is nearly 2 mB.
Here you find the pdf: [url removed, login to view]
Here the actual webpage: [url removed, login to view]