Holiday Database

The aim of the project is to provide a tool to hold relative holiday information of employees. To ensure proper monitoring of employees holiday details by the employer and to provide an easily accessible channel for employees to communicate their holiday plan with employer. Tools: Hardware required for the project will be a computer system (a server, database server and end user machine). Software required for the project will be access to create the database, asp to link the database to the webpage and all the backend automation. (This will make the connection between the backend/front-end more secure). I will use Macromedia Dream weaver (HTML coding) to create the tool – user’s front end. To use the tool a standard computer system is required that the employees can access at all times. The system has to have access to the company’s Intranet where the tool will be based. The system does not have to be internet accessible. The tool will be based on window operating system; therefore the software needed to build the tool has to be platform, which is independent on window IE compatible. User There are three different types of users; Admin – Responsible for the validation and updating of all employees relevant information. Manager – Responsible for authorising employee’s holiday(s) requests/cancellations as well as monitoring their team’s availability. Employee – Responsible for requesting or cancelling any holiday(s) and monitoring their holiday entitlement. Every time a link is clicked I want that link to open up within the main frame only NOT a new page. The left hand frame will have the following links: Login, Home, and Admin. There are 19 pages. 1: Main Page 2: Login Page (login and password page). There will be a login for each user (man, admin, emp). The Managers login will start with MGNR the admin ADMIN and employees login with EMP. Each member of staff will have a unique login no from then on in order for them to access their own personnel information. If a user has difficulty login in they will have the option to send a message to the AMDIN team to reset their password. This will be an automatic process. Only 3 attempts will be allowed for each user to login. After this time they will be timed out and a message displayed telling them to contact the admin team with a no. ach user will be prompted to change their password every two months for security reasons. 3: Employees Login Page: Login and password screen here. Employee’s login will start with EMP and have unique numbers afterwards. 1: Holiday Request Form 2: Holiday Cancellation Form 3: Check Status of Holiday 4: Holiday Entitlement 5: View Holidays Taken 6: Change password 4: Holiday Request Form This will be a link that when selected the employee can complete the holiday request form. Once completed the form is saved. Once the holiday has been approved by the employee’s line manager then the form is sent back to the employee with either an approved or declined inserted into the form. Once accepted the holiday entitlement table for that employee is updated. 5: Holiday Cancellation Form Here the employee will put in the holiday request form ID no and the holiday form will come up. The employee then selects the cancellation button and the submit button. It then gets sent to the manager for approval and once approved is sent back to the employee to view. Again once the holiday cancellation has been approved the holiday entitlement table is updated. 6: Check Status of Holiday Here the employee can check the status of their holiday to see if it has been approved or declined. They simply input their holiday ID no and the details are brought up to view. If declined there will be a message by the manager to state why it has been declined. Employee also checks the status of a cancellation form. 7: Holiday Entitlement Here the employee can view their holiday entitlement for the year and every time the employee’s holiday has been accepted the holiday entitlement value is changed. (e.g. 248, employee takes 40 hrs then holiday entitlement is 208). 8: View Holidays Taken Here the employee can view all their holidays they have already taken or are going to take. This is read from the rota table but the employee can only view their own rota. This corresponds to the employees login ID no. 9: Change password Here the employee has the option to change their password. They will be asked to input their new password twice. 10: Manager Login Page Login and password screen here. Manager login will start with MGNR and have unique numbers afterwards. 1: View Holiday Requests 2: View Holiday Cancellations 3: Change password 11: View Holiday Requests This link will show the manager all the holidays that have been requested by their employees. It will list each employee’s holiday request form. There will be a button for the manager to select his team’s rota only. This will be displayed on a separate window and will be the only page to be opened in a new window. E.g. of rota. There will be a calculation here to state how many members of the team are off as only 20% can be off at anyone time along with a list of the band periods for that year. The band period is two weeks in December one before Christmas and one after Christmas and one week leading up to Easter. Once the manager has checked that the holiday can be approved they select the accepted button if not the decline button in which they can add any comments to state why it has been declined. The manager can then manually update the team’s rota to put that team member down as holiday. Once the holiday has been accepted the employee’s holiday form is updated to accept or decline within the View Holiday Taken link. 12: View Holiday Cancellations This will work in the same way as above but one the holiday can be cancelled when the team rota for that employee is updated to IN not HOL manually by the manager. Again the employees form is updated and sent to the employees View Holiday Taken screen. 13: Change Password Here the manager has the option to change their password. They will be asked to input their new password twice. 14: Admin Login Screen Login and password screen here. Admin login will start with ADMIN and have unique numbers afterwards. If an employee has sent a message to state that they have forgotten their password this message will be displayed once the admin team has logged in. Here the admin team can: 1: Input New Employees Details (input new details of new employer) 2: Delete An Employees (Delete a leaver) 3: Modify Employees details (This can be for a team member that wishes to move into another team. 4: Reset a password. 5: Change password 15: Input New Employees Details Here the admin team will be able to input the details of a new employee. This includes: Employee first name, employee surname, employee DOB, Employee start date, Full or part time, Employee Holiday Entitlement, Employee continuous service date, employee holiday entitlement, employee department, employee team manager, Default password (set at 123456) (As a new employee the system will automatically create an employee ID No). 16: Delete an Employee Here the admin team can delete an employee that is leaving the company. They simply select the department the employee is working in and then select the employees ID no. This will bring up the employees details from the employee database. The admin then has the option of deleting and saving changes or cancelling the option. Also asked if what to continue to delete a different employee. If yes is selected then it will take the admin back to the delete form if no selected then takes them back to the admin login page. 17: Modify Employees Details Here the admin team can modify an employee’s details. They simply select the department the employee is working in and then select the employees ID no. This will bring up the employees details from the employee database. They then make the necessary details and select the save button. If then get the option of continuing to modify another employees details. If yes takes them back to the modify form if no selected then takes them back to the admin login page. 18: Reset password. Here the admin team can reset an employee’s password if they have forgotten it or entered it incorrectly 3 times. They enter the employees ID no and then reset the password to abc123. The employee must contact a member of the Admin team in order to have their password reset. 19: Change Password Again the same screen is available for the admin team to change their password. Save buttons will automatically save the relevant information in the database. Delete buttons will automatically delete relevant information in the database. Reset button will reset the information back. On each screen within each users login there will be the link to take them back to their login page. E.g. Manager goes back to Manager Login Page; Employee goes back to Employee Login Page etc. Security Issues: The login IDs allows each user to view their own login page only. Managers ID to start with MGNR, admin ID to start with ADMIN, employees to start with EMP. The manager will only be able to view their own team’s rota which is determined by their login ID no which is stored in the Manager and Employee table. After two months the system will automatically ask the users to change their passwords. When the employee wants to view their holidays taken they can only view their own holiday rota from the rota table. This will correspond to the employee’s id no. To have a secure database system where no one will be able to access it from the outside. Access Database: Here are the following tables within the database. You can see the relationships within the access database. Manager Login Manager ID Password This holds all the managers within the company’s ID and password. Checks are made when a manager logs into the system to verify that they are a known manager. Employee Login Employee ID Password This holds all the employee’s within the company’s ID and password. Checks are made when an employee logs into the system to verify that they are a known employee. Admin Login Admin ID Password This holds all the administrators within the company’s ID and password. Checks are made when an administrator logs into the system to verify that they are a known administrator. Manager Manager ID First Name Surname Department ID This holds all the managers information and links the department ID to the employee table so that the manager can only view their own team’s rota. Allows their name to be displayed once logged in. Admin Admin ID First Name Surname This holds all the administrators details. So that their name can be displayed once logged in. Employee Details Employee ID Employee First Name Employee Surname Employee DOB Employee Start Date Fulltime or Part time Employee Holiday Entitlement Department ID Password This holds all the employees details and allows their name to be displayed once logged in. Here is where their default password is set as abc123. Here the holiday entitlement is a manual input by the admin team. The holiday entitlement and continuous service inputs need to be correct as the holiday entitlement table will display these calculations. Holiday Entitlement Employee ID Holiday Entitlement for the Year Holiday Entitlement Here is where the employee can look up there holiday entitlement for the year as well as the amount of holiday they have left to take. Rota Employee ID Employee First Name Employee surname Employee Entitlement Department Name Week No Week Beginning Sunday-Saturday Rota Department 1 First week – Thurs, Fri, Sat, Sun (10 hrs shift) Second week – Thurs, Fri, Sat, Sun (10 hrs shift) Department 2 First week – Sun, Mon, Tues, Wednes (10 hrs shift) Second week – Sun, Mon, Tues, Wednes (10hrshift) Department 3 First week – Sun, Mon, Tues, Wed, Thurs (8hr shift) Second week – Sun, Mon, Tues, Fri, Sat (8hrs shift) Department 4 First week – Mon, Tues, Wed, Thurs, Fri (8hrs shift) Second week – Sun, Wed, Thurs, Fri, Sat (8hrs shift) Department 5 First week – Tues, Wednes, Thurs, Fri, Sat (8hrs shift) Second week – Wed, Thurs, Fri, Sat, Sun (8hrs shift) Department 6 First week – Mon, Tues, Wednes, Thurs (10 hrs shift) Second week – Mon, Thurs, Fri, Sat (10 hrs shift) Department 7 First week – Tues, Wednes, Thurs, Fri (10 hrs shift) Second week – Mon, Tues, Fri, Sat (10 hrs shift) This will display each departments rota. A department consists of many employees. Each department has two rotas that are alternated every two weeks. Please see the rota above for departments rota Requirements: • When the user logs in their name is displayed in their login page. • The holiday entitlement database table is to be updated each time a holiday is accepted by the manager. The hours are taken away from that employee’s entitlement, (decreased). If an employee cancels a holiday and the request is accepted again the entitlement table is updated for that particular employee. (Entitlement is increased). Entitlement needs to be updated within the Holiday Entitlement and Rota Database Table. Also here a calculation needs to be held in which the holiday for the year can be worked out for each employee. This is taken from the Employee Details table using the Employee Holiday Entitlement and Employee Continuous Service rows. If the employee has over years service within the company then their holiday entitlement will be Employee Holiday Entitlement + 10 hrs. Please note that the employee entitlement is manually worked out by the admin staff and inputted into the database. • Time and date to be displayed on the home page. • The rota is proving a problem as it has to be completed for the full commencing year (01 April – 31 March). Each department as a rotating weekly rota as stated above. Do not want to have a separate rota for each department as this will mean duplication within the database which is not allowed. Any ideas would be appreciated. • When the employee wants to look up what holiday they have already taken, they can only look at their own holiday rota, (read only). This will correspond with their login ID which will identify who they are and what team they work in. Want the employee to be able to look up this information through the rota. • Employees are also able to look up what holiday entitlement they have left to take as well as what they had for the year. They will access this information through the entitlement table but again only be able to view their own. This will correspond with their login ID etc. • Managers are able to only view their own team’s information. This will correspond with their login ID. The manager needs to be able to look up the team’s rota to check availability of the team’s rota in order to grant the holiday request(s). • When an employee requests for a holiday(s), and submits the completed form, the Manager has to check that the employee has enough holiday entitlement to be able to take the requested holiday as well as checking that there are enough members of the team in (20% only allowed off at one time). • Login details must be EMP (unique number) for the employees, ADMIN (unique number) for the admin team and MGNR (unique number) for the managers. I do not know how to set this within access. Any information or ideas will be appreciated. • All employees passwords are to be set as a default password abc123. Please note that I have already designed the website and database using access. I just need to link the two together to run queries et Skills: An asp programmer to link the designed website to the database designed in access. No longer than 2 to 3 weeks to complete