PDA Data collection Software + PC database application

BRIEFING FOR SOFTWARE DEVELOPMENT FOR PDA/HANDHELD (front-end interface for datacollection) AND PC (database-application) 1. Profile of company/contractors for: PDA barcode scanning & Database programming a. PDA & Database development: Using PC software / Windows CE software such as Microsoft Access & Microsoft Map point b. Master English + able to incorporate texts (Copy paste) in other languages c. Communicate clearly and precise d. Handover not only final materials (PDF/HTML etc) but also the programming code and source files. e. Work based on a fixed flat fee per predefined task 2. Description of our company – in order to improve the understanding about what we do a. C&Co is a company in Belgium (Europe) that offers coffee & hot beverage vending machines, lemonade in cans or bottles in vending machines and candy bars / Sandwiches etc in vending machines installed in companies and schools b. We apply 2 types of service: 1. Premium Service 2. Full Service PREMIUM SERVICE: C&Co operates small coffee machines that prepare fresh specialty coffees such as espresso, cappuccino etc in companies Each machine has a separate and unique EAN barcode. C&Co (further referred to as “CNCO”) delivers and sells the consumables etc on a subscription base or via a rental formula (for the machine) + the sales and delivery of the consumables + a technical maintenance contract CNCO’s business model generates income through: 1. The one time sale off vending machines + the consecutive Sales off consumables (such as can, bottles, coffee, milk, cocoa powder, plastic/paper cups etc) + through a technical maintenance contract 2. A monthly subscription + a monthly fee to cover the above costs + the cost off the machine. This model is the most popular since the consumption – expressed in number of cups consumed per day – determines the price per cup. We apply “buy more – save more” pricing, we provide stock and delivery management and require a yearly check of consumption + a yearly payment of surplus consumption as well as a early warning system in order to advise the customers a lower rate per cup, a larger capacity vending machine etc FULL SERVICE: CNCO operates larger capacity vending machines for hot beverages & Coffee, cans and bottles of soda and refrigerated drinks and candy bar vending machines. The gross margins on these products are very low, CNCO therefore needs to be highly efficient at all times Some customers are entitled to a commission based upon their consumption Full service vending machines produce income by means of coins or banknotes entered in the vending machines directly or via our electronical chip-payment system. This income needs to be checked against the consumption of consumables by each of the machines separately in order to avoid machines installed at locations without enough sales or fraud by staff. 3. Requirements / Description PDA barcode scanning & Database programming We seek a party that can program 4 portable hand scanners / PDA operating a mobile Windows operating system and feed the data in a database application with following specifications (to be fine-tuned in a dialogue according your skills and expertise) GOALS 1. Program PDA software and a PC database application 2. Register delivery data, maintenance or technical interventions and or changes in customer data 3. Semi-permanent communication between drivers and HQ in real-time / semi real-time in order to exchange 1. New delivery or service tickets 2. Execution status of tickets: deliveries, service tickets, updated customer data 3. Status control (work status) driver: resting, delivering, traffic jams etc 4. Integrated GPS navigations (Optional) • Handheld functionality to scan the EAN barcode of delivered products at the customer and to allow the customer to sign for receival on the PDA using a stylus • A page containing 1 unique barcode per type of consumable could be attached in each vending machine so when the machine is filled with consumables the amount per product that was refilled could be entered in the PDA for each product and per vending machine, resulting in: • A date (the day of filling) • Quantity per product refilled (barcode + a quantity) • And deducted from this the real-time inventory level per vending machine: after a one time initial (or yearly) manual inventory-count, the database will calculate off-line on a pc:  The consumption per product selection  The ideal visit frequency in order to minimize visits and travelling time to locations / machines that require less refills yet avoid at all cost stock break in the machines since if stocks run out, the machine provides no income and the customer perceives this as poor service.  The income per machine: All products sold need be refilled, therefore the sum off all the quantities per product selection, for all products together per vending machine multiplied by the price per product selection should be the income that the machine has generated since the last refill. This allows therefore to count the income and check whether the coins in the machine balance with the theoretical income (if not: this reveals fraud or theft) • Inventory management: 1 master inventory (warehouse), vans + driver, deliveries to customers, reception of goods from suppliers at the depot, iron stock inventory management in order to determine re-ordering of products from 5 suppliers • Deliveries need to be traceable / linked per vending machine (unique barcode per machine) • Trace income / receivables per vending machine and per customer and per delivery route or per merchandiser • Feed to the invoicing system and general ledger since for Premium Service customers the amounts delivered are grouped per month and result in an invoice • The inventory per machine, per van and the total inventory in the warehouse needs to be updatable in a measurable and simple way • Planning of delivery routes + generation of delivery papers per customer • Any delivery, refill or technical intervention (a ticket) that is uploaded daily on the hand scanners (a route) needs to be approved/confirmed by the customer by means of his signature + a timestamp that are stored with the data of the ticket (status, quantities, future actions required etc) • Preventive maintenance is undertaken in order to maintain vending machines / preventively change certain parts / descale certain parts etc per machine and per customer. We need to prepare routes or visits for this and be able to retrace the history per machine and or per customer. Attention: sometimes in the lifetime of a vending machine it can be moved to another customer. • A typical work-day with the PDA: • Morning: a delivery route, containing individual delivery tickets or service tickets in uploaded in de the PDA (wireless? Via GPRS? Via a cable? Via internet?) • Our driver/technician compares the stock-level in his delivery van with his needs based upon the tickets that were given to him, using a function of the PDA. The PDA instructs hum what parts and or products to load or to unload. • When he visits tickets from Premium Service customers, he scans the product and enters the quantity of each product + any other technical intervention or service he provided He also scans the barcode on the machine and enters service related data on the machine in order to manage the machines. The execution of the delivery and service ticket is approved by the customer by means of a signature. The status of each ticket in stored (see further) • When he visits a Full Service customer, per individual machine he scans per product the amount that he fills, a descriptive status about the machine + whether he empties the funds collected by the machine • At night the PDA is synchronized with the Access database application by means off Wi-Fi/GPRS/Docking station + internet • The service man has the possibility to manually adjust the stock level in his vehicle, but this intervention should be flagged • Stock delivered by our suppliers should be managed in the same way • No product can leave the warehouse or a van without being scanned and linked to an inventory move / or without a delivery ticket to a customer / or without a delivery voucher from a supplier • Tables are maintained either on PDA / either on the central PC:  Products + barcode + descriptive data + name of supplier  Customer table  Table of Vending Machines , their unique barcode , coupled to maximum 1 customer, contents (selection of products offered in the machine), description of machine, maintenance or intervention data, pricing info of products offered for sale  Table of technical interventions: several types of interventions are possible, always linked to individual machines  Table contains stock per product per machine, per van, per warehouse  Table of vans, warehouses • The PC application allows:  To plan delivery routes / technical interventions per date  Synchronize (export delivery data to our invoicing software)  Check the status of the routes and tickets  Collect statuses and future to-do’s from the PDA  Collect sales statistics and collection of funds from each machine + compare statistics from the past + easy comparison with physical count of earnings to detect fraud or theft + compare refills with collection over time to balance out inaccuracies.  This needs to be user friendly, simple and easy to maintain. Specifications PDA-equipment  make and model to be determined together, but to be purchased by us in Belgium 4. “readable” color screen, at least 3,5” 240x320 with touch screen 5. Sufficient anti scratch en shock resistant screen 6. Integrated Laser barcode scanner with support for all UPC & EAN codes 7. Solid and portable design: IP64 water-resistant, 1,5 meter drop-resistant, 8. Weight: max 350 grams 9. Numeric keyboard with Alfa-functions 10. 128 MB RAM 11. Microsoft Windows Mobile 5.0 or higher, preferably hardware compatible with Windows Mobile 6.x 12. Battery with autonomy of 12 hours in continuous operations; second spare battery should be foreseen 13. USB interface 14. GPRS 3-band communication module (optional) 15. Integrated GPS module  optional  depending on whether you can provide location data on MS Map Point maps 16. Cigarette Lighter power + attachment to windscreen 17. Protective poach enabling all functions 18. 5 styluses per PDA 19. 3 year on site warranty NBD on-site Additional functionalities / descriptions of functionalities • The PDA of the drivers will be ideally connected in real time to the CNCO HQ. There are currently 4 drivers with their own route Additional drivers will be added later on • In the HQ we require a point of access (web services with dedicated database in DMZ) that contains all data about tickets (deliveries, maintenance and customer data related to these tickets). This point of access should only contain current data (1 week to 3 days ahead for data security This point of access communicates in real time with a database application in Microsoft Access • MANAGE TICKETS Each PDA is fed fully automated the planned tickets (deliveries and service interventions) + customer data Should we add additional tickets for the same day or tomorrow using the MS Access database application, these should be uploaded or changed automatically in the appropriate PDA See detailed file structure for data that needs to be updated / synchronized between PDA and MS Access database application • REGISTER EXECUTED TICKETS The driver registers for each ticket the delivered quantities (quantities in separated fields in plusses or minuses!), the serial number of the vending machine that was serviced, eventual changes in customer data, the customer signature (touch screen in BMP format of 32 Kb), “reason codes” of non execution and future to-do’s. The data of executed / changes tickets needs to be exchanged / synchronized asap with the point of access in the HQ The point of access immediately synchronizes with the MS Access database application See detailed file structure for data that needs to be updated / synchronized between PDA and MS Access database application • REGISTER STATUSCHECK OF DRIVER The drivers must at all times be able to enter their actual status on the PDA: resting, delivering, traffic jam etc In addition (optional) the PDA must exchange every 10 minutes the GPS geocode of the PDA via the point of access and coupled to MS Map Point maps with the possibility to zoom in on the location of the drivers The routing history with Status and GPS locations must be stored for at least 2 months and must be accessible for reviewing • GEOCODE CUSTOMERS The driver must be able to store the exact GPS location of each customer in his route This geocode-data needs to be exchanged / synchronized asap with the point of access in the HQ. The point of access immediately synchronizes with the MS Access database application • INVENTORY MANAGEMENT AT CUSTOMERS The driver must be able to see a warning whether he should do a stock take of a customer This indications needs to come from the MS Access database application and needs to be exchanged / synchronized asap with the point of access in the HQ. The point of access immediately synchronizes with the MS Access database application and vice versa 4. TERMS & CONDITIONS a. All quoted prices need to be detailed including remote training of 1 day per PDA and 3 days for the MS Access Database application. No VAT should be applicable b. The contractor needs to develop the coupling and synchronization between data access point, the MS Access database application and the PDA. This coupling needs to support triggering on table/field level. c. The PDA software as well as the Data Access Point as well as the MS Access database control application needs to have a 30 day user acceptance phase before payments are made. Any adjustment required to fix bugs/shortcomings prolongs automatically this period d. Payments will only be made after full operational delivery of the software e. The software needs to be covered by a full-warranty service contract guaranteeing that shortcomings/bugs will be solved by the contractor, this without additional costs during 2 years after delivery of the software f. The source code of each of the 3 software developments (the point of access software, the PDA-software and the control tool written in MS Access) need to be delivered to CNCO as part of the delivery. g. CNCO prefers software development in Visual Studio 2008 (Visual C# or Visual Basic) by using LINQ and ASP.NET 3.5, in combination with MS Access and MS Map point h. The software for the data access point needs to run on a standard MS Vista or MS Windows Server 2003 Standard 32-bit with IIS 6.0 (or higher) PC Suggested Details for Filestructures in PDA 1. Customerdata Field Name Data Type Length Modification allowed Customer No. Code 10 NO Customer Name Text 30 NO Customer address Text 50 YES Customer Post Code Code 20 YES Customer city Text 30 YES Customer Comments Text 80 YES Customer E-mail Text 100 YES Customer Phone Text 250 YES X_coordinate Code 10 YES Y_coordinate Code 10 YES Monday Nonworking Boolean YES Monday Morning Start Time YES Monday Morning End Time YES Monday Afternoon Start Time YES Monday Afternoon End Time YES Monday Night Start Time YES Monday Night End Time YES Tuesday Nonworking Boolean YES Tuesday Morning Start Time YES Tuesday Morning End Time YES Tuesday Afternoon Start Time YES Tuesday Afternoon End Time YES Tuesday Night Start Time YES Tuesday Night End Time YES Wednesday Nonworking Boolean YES Wednesday Morning Start Time YES Wednesday Morning End Time YES Wednesday Afternoon Start Time YES Wednesday Afternoon End Time YES Wednesday Night Start Time YES Wednesday Night End Time YES Thursday Nonworking Boolean YES Thursday Morning Start Time YES Thursday Morning End Time YES Thursday Afternoon Start Time YES Thursday Afternoon End Time YES Thursday Night Start Time YES Thursday Night End Time YES Friday Nonworking Boolean YES Friday Morning Start Time YES Friday Morning End Time YES Friday Afternoon Start Time YES Friday Afternoon End Time YES Friday Night Start Time YES Friday Night End Time YES Saturday Nonworking Boolean YES Saturday Morning Start Time YES Saturday Morning End Time YES Saturday Afternoon Start Time YES Saturday Afternoon End Time YES Saturday Night Start Time YES Saturday Night End Time YES 2. Tickets/Orders Field Name Data Type Length Modification allowed customerID Code 20 orderNumber Code 20 RouteID Code 10 RouteDate Date productID Code 20 product Text 150 3. Executed deliveries Field Name Data Type Length Modification allowed customerID Code 20 NO orderNumber Code 20 NO RouteID Code 10 NO RouteDate Date NO productID Code 20 NO product Text 150 NO delivered Quantity Decimal Yes returned Quantity Decimal Yes delivered Quantity bck Decimal Yes returned Quantity bck Decimal Yes serialno Code 20 Yes Reason Not Delivered Code 20 Yes Future ToDo Code 20 Yes Future ToDo DueDate Date Yes