代写COMP3297、代做SQL设计、代写Supply-Pilot、SQL编程代做

日期：2018-11-15 10:22

COMP3297 Introduction to Software Engineering

Department of Computer Science

The University of Hong Kong

PROJECT DESCRIPTION:

Air Supply-Pilot: drone-based delivery management

Background:

This is a pilot project (that is, an initial small scale implementation) for a much larger and more complex

system named Air Supply. Air Supply is a large-scale project for the Hospital Authority (HA) to centralise

warehousing and distribution of medical supplies such as pharmaceutical products, surgical supplies, blood

products, etc. It will use aerial drone delivery technology to facilitate just-in-time inventory management for

smaller HA facilities such as out-patient clinics.

The HA manages 48 General Out-patient Clinics in addition to managing Hong Kong’s public hospitals. Just

like hospitals, clinics maintain their own inventories of medical supplies. But the rate at which clinics

consume them is comparatively low and many of the more specialised items in clinics’ inventories are rarely

consumed at all. Inventories occupy considerable space at clinics and require additional staff to manage their

storage. They also lead to unnecessary wastage of supplies that have limited shelf-life; every clinic

maintains its own stock of such supplies and destroys them if they are not used before their expiration date.

The Air Supply project will eliminate the need for clinics to maintain large inventories of supplies by

centralizing warehousing at a small number of major hospitals, each of which will supply the clinics in its

area. For instance, clinics in the HA Hong Kong East and West clusters will be supplied from Queen Mary

Hospital, and clinics in Kowloon West, Central and East clusters will be supplied from Queen Elizabeth

Hospital.

Air Supply will make it possible for clinics to order supplies online for delivery by drone and will

completely automate the delivery process. The following is a summary of planned workflow:

A Clinic Manager uses the Online Ordering Service to place an order for supplies and indicates a

priority (reflecting the urgency of the order). The order is given a unique order number and is

queued for picking and packing at the automated warehouse of the clinic’s supplying hospital.

Order-picking robots pick and pack the order items into a lightweight shipping container and

attach a RFID tag to identify the order and its destination. The container is then queued for

dispatch.

When a drone or drones is/are available to make a delivery and there is at least one container

queuing for dispatch, the next drone in turn is loaded with containers from the queue either: (a) up

to the load carrying capacity of the drone, or (b) until no more containers remain in the queue.

Shortest-route planning is used to generate an itinerary from the set of destinations to which the

orders must be delivered. The itinerary defines the route the drone will fly to deliver its orders. The

itinerary is loaded into the drone’s Autonomous Drone Control System and the drone begins its

delivery run.

The Clinic Manager can track the progress of an order through the various stages of the

dispatching process by checking its status.

At the two points at which orders are queued (prior to picking and packing, and prior to dispatch),

orders are queued by priority and, for orders with equal priority, by the date/time at which the

order was placed.

When the drone arrives at a clinic’s delivery point, the orders for that clinic are identified by their

RFID tag and are unloaded. An automated battery-swap is performed and the drone then

continues to the next destination in its itinerary. The last destination in an itinerary is always the

drone’s home hospital where, after an inspection and battery-swap, the drone is queued to be

loaded with orders for its next delivery run.

A further advantage offered by Air Supply is that at times of natural disasters or when ground transportation

is compromised (in the aftermath of typhoons, for instance), clinics can serve as local Accident and

Emergency centres, with necessary supplies delivered by drone. This will be particularly beneficial for

residents of out-lying islands. Similarly, in cases of rescue or other situations in which medical supplies

may be needed in remote areas, the location can be added to the system temporarily and be served by drones.

Several systems and services will be developed and/or integrated to create Air Supply, including an Online

Ordering Service, the Warehouse Control Systems, the Autonomous Drone Control Systems, and the Route

Planning Service. First, however, the Hospital Authority requires a reduced-scale system (that is, a pilot

system) to enable it to evaluate the concept and gather operational data.

The Pilot System, AS-P:

The pilot system, AS-P, is the focus of your project. Compared with Air Supply, AS-P will have greatly

reduced functionality and limited automation. The majority of tasks that will be automated by Air Supply

will be handled manually in AS-P. It will be delivered as a single self-contained web application that

provides basic ordering, loading planning and route planning services.

The pilot system will serve only a small number of clinics selected from the Southern District and the

Islands. Supplies for those clinics will be warehoused and dispatched from Queen Mary Hospital.

Initially, the pilot system will handle only a single category of medical supplies: intravenous (IV) fluids such

as Normal Saline and Lactated Ringer’s. After a period of monitoring and evaluation, the categories of

supplies handled by AS-P will be expanded to cover the full range of drone-transportable medical supplies

needed by clinics and emergency services. Full item catalogue management, including search, will be added.

The number of drones in service will be increased accordingly and a second stage of monitoring, tuning and

evaluation will begin.

It is envisaged that the major components of the AS-P system will ultimately be separated out to form, for

example, the core of the Online Ordering System and the Route Planning Service of Air Supply.

Additional Details of AS-P Workflow:

Given that many actions will be performed manually, the AS-P workflow differs from that of Air Supply:

Picking and packing is performed manually by Warehouse Personnel;

In place of an RFID tag to identify order containers, Warehouse Personnel print and attach a

shipping label. The shipping label is generated as a PDF file by the system;

Loading containers onto drones is performed manually by a Dispatcher.

Your Client:

Your client is MyTutors, a Hong Kong-based company originally specialising in online educational support

systems that has diversified into general web application development. MyTutors was the client for the

Tutoria tutor brokering platform and the imageX image exchange platform developed by our COMP3297

teams last year. The principal contractor for the AS-P system has approached MyTutors to develop the

software sub-system.

MyTutors has a small number of in-house developers and technical support staff, however their time is

currently fully committed to other projects. The company is sub-contracting your team to develop the first

version of the AS-P web application.

Note that even though you have a client for this project, your team retains intellectual property rights related

to the software you produce – it belongs to you.

Your contact in MyTutors:

Your principal contact at MyTutors is Mr. Paul Chan, Chief Technology Officer. We expect you to deal with

him in a professional manner throughout the project and this will contribute to your grade.

You may contact Mr. Chan at: paulchan1116@gmail.com.

We have a large class this semester. In common with the majority of real-world clients, Mr. Chan has many

demanding responsibilities and won’t respond well to open-ended questions that require long, detailed

answers. For example, to ask: “Please summarize your requirements for the AS-P” would be unacceptable.

Likewise, clients don’t like to receive large numbers of questions. Aim to act professionally - respect your

client’s time and make good use of any scheduled contact you have with him.

Details obtained from initial interviews:

MyTutors’ business analysts have already conducted interviews with representative stakeholders and have

elicited the following details:

a) User needs (here User refers to any new user, regardless of role, who first must register)

Receive a token (or similar) by email to their HA account enabling them to register as a user of ASP,

but only in a particular role (for example, as a Clinic Manager);

Use the token to register, specifying:

o a username and password for subsequent authentication;

o first name and last name;

Have their HA email address (the address to which the token was sent) added to their registration

by default;

After registration, be able to log in and access AS-P features available for their role;

Change password, email address, first name or last name;

Regain access to their account if they forget their password.

b) Clinic Manager needs

On registration, specify the clinic they manage;

Browse, by category, descriptions of supplies available for drone delivery from the supplying

hospital and view additional details (currently only shipping weight) and an image of each item;

Construct an order by selecting items of supplies and specifying the quantity required;

Place an order with their supplying hospital, specifying its priority. Initial order status to be set to

Queued for Processing;

View orders for their clinic that have not yet been delivered and view their current status;

Cancel an order;

Receive email notification when their orders are dispatched by the hospital;

After receiving delivery of an order, notify the system. Order status to be updated to Delivered.

c) Warehouse Personnel needs

View the priority queue of orders that have been placed by Clinic Managers but for which

processing (that is, pick and pack) has not yet begun.

Remove the order from the top of the queue to pick and pack its items. Order status to be updated to

Processing by Warehouse and the queue updated;

View details of the order removed for picking and packing;

Obtain a shipping label for the order in the form of a PDF file for printing;

Notify the system when processing of an order is complete. The order to be added to the dispatch

queue. Order status to be updated to Queued for Dispatch .

d) Dispatcher needs

View the orders to be loaded onto the next available drone - that is, the orders that can be removed

from the dispatch queue, in order, and loaded without exceeding the load carrying capacity of the

drone;

If there is at least one order, download a file from the system in CSV format specifying the itinerary

for order delivery. The Dispatcher will upload the file to the drone. (Uploading is not a required

feature of AS-P; it will be handled manually in the pilot system.)

Update the status of order(s) just loaded to Dispatched. For each order, the system must email the

PDF file containing the shipping label to the corresponding Clinic Manager as confirmation.

e) Admin needs

After receiving a lost password request, send mail to the user’s registered email address containing a

token or link for password reset.

f) Health Authority needs

Details of all orders placed via the system must be retained by the system for later audit;

To support evaluation and tuning, the following must also be retained in addition to date/time at

which the order was placed:

o date/time at which the order was dispatched;

o date/time at which the order was delivered.

g) Details of additional business rules and other information

General assumptions:

You may ignore the possibility of race conditions;

You may assume that all users act in a disciplined way. For instance: that Clinic Managers specify

true priority when ordering; that Clinic Managers notify the system as soon as orders are received

(this will be automated in Air Supply); etc;

You may ignore the possibility of system or network failure and downtime;

You may assume that the hospital warehouse always has sufficient stock to satisfy all orders;

You may assume that each user has a single role;

You may assume that all system clocks are synchronized and correct (not an issue for AS-P);

You may assume that in the initial pilot system there is no need to provide support for adding new

medical supplies to the catalogue. All items and their associated data will be added manually by

admin;

You may assume that you will be supplied with all necessary data related to medical supplies,

locations and inter-location distances.

User Accounts:

Usernames must be unique;

New user accounts are always created to include the email address to which their initial token was

sent. This is assumed to be the contact address for the new user. Users can change that address if

they wish, however all users must maintain a valid contact address;

Ordering:

If a Clinic Manager begins to select items for an order, but, for example, logs out before placing the

order, there is no requirement to preserve the incomplete order;

An order can only be cancelled while its status is still Queued for Processing;

The combined weights of items in an order plus the weight of a shipping container cannot exceed

the load carrying capacity of a drone;

Orders can be assigned one of three priorities: High, Medium, or Low;

Orders status can be one of: Queued for Processing, Processing by Warehouse, Queued for

Dispatch, Dispatched, or Delivered.

Container Weights and Loading Capacity:

The lightweight shipping container into which each order is packed weighs 1.2 kg. Thus the

shipping weight of an order is the sum of weights of all order items + 1.2 kg;

The load carrying capacity of a delivery drone is 25 kg.

Medical Supplies Data

The following data are maintained for each item of supplies:

o Category (for the initial pilot, this will be fixed as IV Fluids);

o Description (example: B Braun Lactated Ringers 500ml);

o Shipping Weight (example: 0.52 kg);

o Image of the item in JPEG format.

Location Data

The following data are maintained for each location (e.g. Clinics and supplying Hospitals):

o Name (example: Queen Mary Hospital);

o Latitude (example: 22.269660);

o Longitude (example: 114.131303);

o Altitude in metres (example: 163).

Shipping Label

The following data are required on each shipping label:

o Order Number;

o Contents (that is, a list of the items in the order);

o Name of the order’s final destination.

Itinerary Data

The itinerary to be uploaded to a drone is in the form of a CSV file containing, in order, the

coordinates (latitude and longitude) and altitude of each delivery location on the drone’s route – thus

three values per location. The last location in the itinerary will be the drone’s home hospital which,

for AS-P, is Queen Mary Hospital.

Route Planning

In general, this is a hard optimization problem. For later versions of Air Supply where Dispatchers may wish

to load and route orders optimally when using multiple drones simultaneously, it generalizes the Travelling

Salesman Problem (TSP).

In AS-P and initial versions of Air Supply, drones are loaded and routed sequentially and the problem is a

pure form of TSP. Fortunately, the number of locations a drone must visit on any delivery run is small and

the problem is manageable.

An Itinerary generated by the system is an ordered list of locations to be visited on a delivery run. Thus it

specifies the sequence of itinerary legs to be flown by a drone when making a shortest-distance round trip

that visits every delivery location for the orders loaded on the drone. As a small example, if a drone is

loaded with 4 orders to be delivered to clinics A, B, C, and B respectively, then based on the distances of all

potential itinerary legs, the route planner may compute the best itinerary to be (or, equally, the reverse of):

Leg 1: Queen Mary Hospital → B,

Leg 2: B → A,

Leg 3: A → C,

Leg 4: C → Queen Mary Hospital.

The corresponding CSV file would contain coordinates and altitude of locations B, A, C, and Queen Mary

Hospital, in order. The starting location is not recorded in the itinerary file.

The principal contractor for Air Supply has empirically determined the flying distance between all pairs of

locations served by AS-P. Thus the distances of all possible itinerary legs are known and determining an

itinerary for a given set of orders is not too challenging.

To decide among itineraries of equal distance, AS-P should select the itinerary that delivers the highest

priority orders earliest in the route. If there are still equally suitable candidates, then any one of them can be

chosen.

f) Technical and other constraints

AS-P will be implemented in Python on Django 2.x;

There is no constraint on choice of OS;

It will be sufficient to implement on the Django default development server. Depending on course

progress we may deploy on a cloud platform in the final iteration, but this will not affect your work

in earlier iterations. For simplification, you can also use the default development server with Django

defaults to serve static files on the understanding that it is not a real production-strength option. For

production, we would deploy them to a static file server or Content Delivery Network;

AS-P will be built with SQLite as its DBMS;

As part of its services, AS-P is required to send mail to users. Again, for convenience of testing and

demos, it will be sufficient to configure Django to redirect all emails to the console or to a file

backend. This is not a constraint, however, and you are free to configure to use an actual mail server

instead. You may use any email accounts in place of HA accounts.

You may assume you are free to use any of Django’s built-in resources and third-party applications

to implement AS-P. In fact, you are encouraged to do so. djangopackages.org is good starting point

when looking for third-party solutions – there are solutions for pretty much everything.