Automated plans for interview done on different busses

Develop an automated system that allocates bus lines to interviewers based on their locations, interview quotas, and available transportation modes (buses, trains, metro, and walking).

Key Features

Import from Excel for interviewer locations and bus line data with quotas for interviews.

A module for fetching and updating transportation data via API.

An optimization algorithm for route planning.

Output to Excel in specific format.

Specific Scheduling Requirements

Interview Frequency: An interviewer can conduct approximately one interview every 5 minutes.

Interviews Per Bus: A maximum of 8 interviews is allowed per unique bus on a specific bus line per day. There is no identifier for each unique bus, so the system needs to be able to plan to catch another bus, based on the logic that another bus in route at the same time is definitely another unique bus.

Maximum Interviews Per Line: A total of 16 interviews can be conducted per bus line per day. To exceed 8 interviews on a line, planning must ensure different buses are used, unless no alternatives are available.

Bus Stop Hubs: Certain bus stop hubs where many lines intersect should be prioritized for changing between different bus lines.

End Station Switching: If optimal, the system should plan for switching to another line at end stations or prefer a switch to the same line with a bus that departed earlier to ensure a new unique bus is used for interviews.

Data Requirements

Excel overview with specific targets per bus line and bus line contract group (there can be several buses in one contract group and the goal is reached, when the target for the group is reached.

Certain buses have weekly, monthly or quarterly goals.

Excel sheet with interviewers locations and working times. Interviewers will be able to start up to 30 minutes from their address so all shifts should not start exactly at their address. Will also include suggestions for hubs they can start at.

Excel sheet with an overview of most bus lines and their stops are provided in up to 4 Excel files. These sheets can be large and there might be some lines that are not included. To get the missing lines, API calls should be made to update the files with missing information. There might also be some stops missing for certain lines, API calls should also be used to update these. The files are set up to show all times for one weekday, one Saturday and one Sunday per month, as these times normally are the same for those days during a month. So API calls for missing information and lines should update across the selected weekday, Saturday and Sunday for that month. These updates should be done once a month, and once they are done, the files can be used for that month.

The output needs to be in Excel, in a specified format.

Several different plans should be available for each combination of bus lines, so there is flexibility in choosing what shift to plan and to ensure that is not always the exact same times and from the same station to the same station for each bus. Some variance is needed, both in terms of the direction the bus is taken and between what stations. The system should somehow include this and a part of the solution should be multiple shift suggestions.

Technical Requirements

Open source and free programs like python are preferred. All coding needs to be set up so I will be able to run these even though I do not have any coding experience.

The programs, platforms etc. should not incur additional costs.

Needs to be able to import data from Excel and give an output in Excel.

The final system should be able to fetch data from Excel files in a specific folder and place the outputs in Excel specified formats in a specific folder.

Performance Criteria: Updating of times for the route planning in the Excel sheets, should not take more than a few hours.

Making the plans and output for these should not take more than a maximum of 1 hour. The output should be up to a 100 or more plans (each plan including trips with buses for 4-8 hours). The faster this can be done, the better, while ensuring that all requirements are met.

How to input information should be user friendly, preferably by typing everything into an Excel file or other user friendly interface.

Use Cases

As the main user, I will update quotas and interviewer availability regularly. At least once a month and probably weekly. Then I will select some of the quotas (bus line contact groups and/or specific bus lines) that should be prioritized in the planning.

Then I should be able to run the scripts to get an output with different schedules for each interviewer and from this I will select which specific plans that each interviewer will get. I will copy these plans into my planning sheet and share it with the interviewers in their Excel sheets.

Lines that need to be covered on certain shifts, should be able to be selected and lines that are no longer needed (because all quotas have been met) should also be able to be deselected so they are not part of the schedules provided.

Project Timeline and Milestones

Timeline: 2-4 weeks

Milestones:

Setup API calls and editing of the Excel sheets to give output needed for the detailed planning. This means enriching the Excel file with missing information for bus lines and stops not included, so there is a complete overview of the times for each line (and all stops) on a working day, Saturday and Sunday. The Excel files can be further split up into (working day, Saturday and Sunday), if the Excel files otherwise will be too big.

Setting up the algorithm for planning specific shifts, that include restrictions in terms of maximum interviews, interviewers locations and changing between specific lines, that are needed.

Output with the plans including 2-3 alternative plans per shift, to ensure that there are variance and the best plan can be chosen.

Test of system and that it works reliably.

Budget and Resources

Budget

Use AI to keep costs low.

Provide a cost for a version that is less automated where specific lines are selected manually, for each interviewer and day.

Bids with no running costs once it is developed will be preferred, unless running costs are low and adds value.

Resources: API calls to [login to view URL] for updating the different Excel sheets.

Python Kejuruteraan Perisian PHP JavaScript Java

ID Projek: #37831774

Tentang projek