2

Hi I want to create a web based resource tracking system which will be used by my manager using php and html. The db i am using is MySql. I have already created tables for employee, project etc. I have to create table to store the weekly resource availability. For eg. an employee A is working in 2 projects this week, and the ratio is like 0.4 and 0.6 (combining both we get 1.00 so he is engaged in project this week completely). Next week he is working on 1 project and his availability for that project is 0.7(so the manager can put him on another project also). I have to store these information for next 20 weeks. I am not getting any logic to implement this idea in DB. The below screenshot is how my db looks like now. The dates are hard coded. enter image description here

Also next week i need to delete the previous weeks information and add 21st week's information for an employee. I completely stuck with this. I saw this link database schema for timesheet but it is some what complex which deals with hours also. I just need the information on weekly basis. Can somebody help me to do this schema ?

  • The ratio you are trying to calculate (1.00), is based on one day? How many hours spend the employee? Give some more information abut your calcultations please. – oNare Jun 28 '15 at 17:41
  • @oNare The time is calculated on weekly basis. for eg. an employee A works on project 1 entire week then it is 1.00, if he works on 2 projects then 0.5 and 0.5 (combining we get 1.00, so he is engaged fully on the week) if he works on project 1 only monday and tuesday rest of the days he is free then the value is 0.4 (2/5) – Sarath S Nair Jun 28 '15 at 17:47
  • Google and download "open source project management software" - check out their schemas - beg, borrow or steal ideas there? – Vérace Jun 28 '15 at 19:04
1

I think this could help you.

I create this structure:

Employee
- EmpID (PK)
- EmpName
- TKID

Domain
- DomID (PK)
- DomName

Project
- ProjectID (PK)
- ProjectName
- ProjectStart
- ProjectEnd
- DomID (Asuming your projects are for Domains)

ProjectType
- ProTypeID (PK)
- ProTypeName

RTS
- ProjectID (PK)
- EmpID (PK)
- ProTypeID (PK)
- Active (PK)
- DateStart (PK)
- DateEnd

I fill the tables with the information you post on the screenshot (Only the RTS information is from 2015-06-01 to 2015-06-09).

Employees:

enter image description here

Domains:

enter image description here

Projects:

enter image description here

ProjectTypes:

enter image description here

RTS:

enter image description here

Query:

        SELECT
        E.EmpID,
        E.EmpName,
        E.TKID,
        D.DomName,
        P.ProjectName,
        PT.ProTypeName,
        ROUND(IFNULL((COUNT(DISTINCT CASE WHEN RTS.DateStart BETWEEN '2015-06-01' AND '2015-06-05' && RTS.Active IS TRUE THEN RTS.DateStart END)/5),0.0),1) AS '2015_06_[01-05]',
        ROUND(IFNULL((COUNT(DISTINCT CASE WHEN RTS.DateStart BETWEEN '2015-06-08' AND '2015-06-13' && RTS.Active IS TRUE THEN RTS.DateStart END)/5),0.0),1) AS '2015_06_[08-12]'
    FROM Employe as E
    LEFT JOIN RTS on (RTS.EmpID = E.EmpID)
    LEFT JOIN Project as P ON (P.ProjectID = RTS.ProjectID)
    LEFT JOIN Domain as D ON (D.DomID = P.DomID)
    LEFT JOIN ProjectType as PT ON (PT.ProTypeID = RTS.ProTypeID)
    GROUP BY E.EmpID;

Result:

enter image description here

The only problem with this is that you have to put the week date parameters you want to calculate. You can make a Stored Procedure that could read eStartDate and eEndDate variables and calculate the weeks between those dates, and all you have to do is make a Dynamic Query that concatenate every week between the parameters.

Example:

CALL SP_RTS('2015-06-01','2015-06-31',1);

Between 2015-06-01 and 2015-06-31 exists 4 weeks monday-friday, you'll need to concatenate 4 columns with those dates. I could help you but I dont know if my post if what are you looking for, if it is, let me know.

  • I tried this method, this one doesn't allow an employee to work on different projects in a week and display each project information in the result. For eg. A works on project 1 (06/30 to 07/02) full day - so (3/5=0.6 on project 1 for that week) similarly he works on project 2 (06/30 to 07/04) - so 5/5=1.00 on project 2 for the same week. I need to display these 2 details. Also I have to calculate the sum of all employees ratio in each week (ie, 0.5+1.5+0.75 etc), that is to be displayed on the bottom part of the report. Is their any way to do it ? – Sarath S Nair Jul 1 '15 at 18:58
  • In that query is just calculating the Start day. I'll check it later today to add a BETWEEN sTart - End dates and multiprojects result. – oNare Jul 1 '15 at 19:11
  • Hai @oNare I am stuck with this. could you please help me to complete this schema ? – Sarath S Nair Jul 6 '15 at 14:39
  • Join: chat.stackexchange.com/rooms/25568/onare-s – oNare Jul 6 '15 at 15:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.