Reparto Manager is a simple and light application made for delivery companies, and its main purpose is to optimize the management of orders based on locations, sale prices, and order priorities. Through an intuitive interface, Reparto Manager will allow you to generate new orders and improve logistics.
Automate delivery of products through the following operations:
- Register a new order given customer_name , Postcode, distance, price, phone and email.
- Choose the best order to deliver: The closest one will be selected; then, by the highest price; and then by date. The order can be removed, call the customer or send an email.
- Choose the best group of orders. A group of orders is composed for those who have the same cod_postal. The group price is the sum of all orders from a particular group, and the selected group will be the one with the highest price. All orders from a group can be deleted or can be selected individually to obtain individual information.
- Show a map with orders locations grouped by zip code and display a delivery route.
- Storage: SQLite Database
- Geolocation: Google Maps
A database is used to store orders with the respective customer information, such as name, zip code, distance, price, phone and email. This information can be retrieve by SQL queries.
SQL has been used, since this application can host a large amount of information, and this technology also makes it easier for us to run complex queries using techniques of nested subqueries or derived tables.
SQL query to obtain the best order:
db.rawQuery("SELECT _id, nombre, telefono, email FROM pedidos WHERE _id = (SELECT MIN(_id) FROM pedidos WHERE precio IN (SELECT MAX(precio) FROM pedidos WHERE distancia IN (SELECT MIN(distancia) FROM pedidos)))", null);
Query to retrieve best group of orders:
db.rawQuery("SELECT codigo_postal, MAX(derivada.total) FROM (SELECT codigo_postal, SUM(precio) AS total FROM pedidos GROUP BY codigo_postal)derivada",null); db.rawQuery("SELECT _id, nombre, telefono, email FROM pedidos WHERE codigo_postal = " + codigoPostalMejorGrupo() ,null);
There previous two query are the same as:
SELECT _id, nombre, codigo_postal, distancia, precio FROM pedidos WHERE codigo_postal = (SELECT derivada2.codigo_postal FROM (SELECT codigo_postal, MAX(derivada.total) FROM (SELECT codigo_postal, SUM(precio) AS total FROM pedidos GROUP BY codigo_postal)derivada)derivada2)
However, Android is not able to process it.
On the other hand, we will use SQL queries to delete orders or find locations that will be placed on Google Maps.
Google Maps API for Android allows us to place markers to locate orders, showing the best route by postal code on the Google Maps view.
Moreover, when clicking on one of the personalized markers, a message will pop up and it will show same information like postal code and number of orders for a particular postal code.
Postal codes are retrieved in advance by SQL queries.
Reparto Manager is composed by 6 different views, including the Splash screen which runs during start up. Views:
- Splash (Splash)
- Main (Main)
- Create Order (CrearPedido)
- Best Order Information (InfoMejorPedido)
- Best Group of Orders Information (InfoMejorGrupo)
- Map (Mapa)