Hi,
I thought I seek feedback from this group on what would be the best
direction creating reporting in CakePHP.
My project that I am working on, is to take resource scheduling excel
spreadsheet and move it over to a database, the reason to this is that
the spreadsheet has grown to big, and excel is prone to user entry
mistakes. We have minimized the number of features that we would like
to have initially, to only a handful, to make it in par with the excel
spreadsheet functionality.
1. User to login to view their assignments
2. Resource Manager to have a login to be able to do the following
2.1 Load project resource request from an Excel Spreadsheet
2.2 Assign resources to projects
2.3 View resources availability
2.4 View resources Utilization
2.5 Basic Reporting
We been looking at different ways to do this, and decide to settle on
using cakePHP, the team has created the authentication and a menu
system that shows menu items based on users type. We have created the
database structure so we can load project request and assign resources
and store them in a utilization database table.
But we run in to a snag in the design in creating the resource
utilization, the team are split on how we should approach it, so we
asking the group for some advice. The ideas we have are:
Option 1. create a database table that are generated at the time off
assigning resources, the table would hold the user_id, date, year,
month, day, day of week, month_name, quarter, quarter_name and
allocation percentage. The only problem we see with this is that user
could be on more than one project in a week. So, when a resource
manager assign a resource, we would have to search to see if a record
exist for that week and then update it, if not existing week add a new
record.
Option 2. use a "Star Schema" for dimension and facts. The "Star
Schema" tables would be updated by a function that is triggered by the
resource manager. The resource manager selects a function update
allocation, which would start of a process of reading records from the
utilization table, and loop around tills all weeks are filled in. The
"star table" would have three tables ResourceDimensions, TimeDimension
and AllocationFacts. I personal think this is the right approach, but
has a few learning curves for us. When it is triggered, it should loop
around the user table, if a user do not have an allocation it would
show 100% available, if a user have many allocation for a week, it has
to be summed up, and stored. The issue is the utilization data, we
have start and end date and an allocation, so if the start is 3 feb,
08 and end is 3 march 08, with the allocation 10%. The user would be
allocated for 10% over that period of time, but how do we use date
function is CakePHP to know what week it is. are there any samples.
Our Excel Spreadsheet has this VB Macro doing the job, instead of
saving it to a database, it saves it to a row/column,
- Have anyone tried to do this in the past, like to learn about the
experience and any pitfalls.
- Is CakePHP suitable for doing "Star Schema" and/or for being the
framework for a resource scheduling tool?
- How would be the best way using the macro functions below, have a
component that runs through, with a model that reads the database and
write to the database and a view that shows when completed, any ideas?
============================= sample VB Macro code
===============================================
Star 'Assumptions
' 1. allocation start date has a value for every entry
Dim PersonName As String
Dim RowIndex As Integer
Dim ColumnIndex As Integer
Dim WeekStartingOn As Range
Dim temp As String
Dim count As Integer
Dim startRow As Integer
Dim RowPos As Integer
Dim allocation As Double
Dim writeRow As Integer
Dim writeColumn As Integer
Dim freeAlloc As Double
Dim writeWorkSheet As String
Dim activeWorksheet As Worksheet
' Variable Initialization
temp = ""
count = 1
startRow = 6
writeWorkSheet = "Availability"
Set activeWorksheet = ActiveWorkbook.Worksheets("Utilization")
' find the correct column to read and write
Do While (activeWorksheet.Cells(startRow, count).Value <> "Week
Starting On")
count = count + 1
Loop
RowIndex = startRow + 1
ColumnIndex = count
writeRow = 3
writeColumn = 5 ' to make room for the title and department
addition
' clear and rewrite Availability worksheet
Call ClearAndRewriteSheet
Do While (activeWorksheet.Cells(RowIndex, ColumnIndex).Value <>
"")
' for each column (week):
' write week heading in the new sheet
Worksheets(writeWorkSheet).Cells(writeRow, writeColumn) =
activeWorksheet.Cells(RowIndex, ColumnIndex).Value
writeRow = writeRow + 1
RowPos = RowIndex + 1
Do While (activeWorksheet.Cells(RowPos, 10).Value <> "")
' loop until there are no more rows. This is determined
by values in the allocation start date column
If (activeWorksheet.Cells(RowPos, 1).Value = "") Then
' if the person name value in current row is blank
then add current allocation % to previous %
allocation = allocation +
activeWorksheet.Cells(RowPos, ColumnIndex).Value
Else
' if person name value is not blank then no summing is
required
PersonName = activeWorksheet.Cells(RowPos, 1).Value
allocation = activeWorksheet.Cells(RowPos,
ColumnIndex).Value
End If
If (activeWorksheet.Cells(RowPos + 1, 1).Value <> "" Or
activeWorksheet.Cells(RowPos + 1, 10).Value = "") Then
' If the person name value of the next row is not
blank or the next row of allocation start date is empty
', allocation % for current role is final
' Determine free allocation and write to Availability
worksheet
freeAlloc = 1 - allocation
'red cells for over allocated people, white cells for
normal
If freeAlloc < 0 Then
Worksheets(writeWorkSheet).Cells(writeRow,
writeColumn).Font.Color = RGB(255, 0, 0)
Else
Worksheets(writeWorkSheet).Cells(writeRow,
writeColumn).Font.Color = RGB(0, 0, 0)
End If
'write allocation to another sheet, write name
Worksheets(writeWorkSheet).Cells(writeRow, 1) =
PersonName
Worksheets(writeWorkSheet).Cells(writeRow,
writeColumn) = freeAlloc
writeRow = writeRow + 1
allocation = 0
freeAlloc = 0
End If
RowPos = RowPos + 1
Loop
ColumnIndex = ColumnIndex + 1
' increament indices for Availability sheet
writeRow = 3
writeColumn = writeColumn + 1
Loop
'--------------------
'calculate allocation for people who are not listed in any deal
tools but are on the company roster
'--------------------
Set utilizationWorksheet =
ActiveWorkbook.Worksheets("Utilization")
Set availabilityWorksheet =
ActiveWorkbook.Worksheets("Availability")
Set statusWorksheet = ActiveWorkbook.Worksheets("Status")
availabilityStartRow = 4
counter = 0
'determine last row that contains a name and use it for
"writeStart"
readRow = availabilityStartRow
Do While availabilityWorksheet.Cells(readRow, 1).Value <> ""
readRow = readRow + 1
Loop
writeStart = readRow
availabilityWriteRow = writeStart
'get how many weeks out the sheet has been programmed to deliver
info
weeksToProcess = utilizationWorksheet.Cells(4, 2) 'B4
'loop through all staff members on the status sheet and check if
each of them has been listed yet in the availability sheet. if not,
list them as 100% available
statusStartRow = 3
staffReadRow = statusStartRow
Do While statusWorksheet.Cells(staffReadRow, 1).Value <> ""
If statusWorksheet.Cells(staffReadRow, 2).Value <>
"Terminated" Then
staffMemberFound = 0
'loop through current availability list and see if this
staff member has been listed. if so, ignore them
For availabilityReadRow = availabilityStartRow To
writeStart - 1
If statusWorksheet.Cells(staffReadRow, 1).Value =
availabilityWorksheet.Cells(availabilityReadRow, 1).Value Then
staffMemberFound = 1
' write title and department from the status sheet
availabilityWorksheet.Cells(availabilityReadRow,
2).Value = statusWorksheet.Cells(staffReadRow, 3).Value
availabilityWorksheet.Cells(availabilityReadRow,
3).Value = statusWorksheet.Cells(staffReadRow, 5).Value
availabilityWorksheet.Cells(availabilityReadRow,
4).Value = statusWorksheet.Cells(staffReadRow, 2).Value
End If
Next
If staffMemberFound = 0 Then 'if staff member was not
found in the list, then list them as 100% available
'write the staff member's name into the availability
sheet
availabilityWorksheet.Cells(availabilityWriteRow,
1).Value = statusWorksheet.Cells(staffReadRow, 1).Value
' write staff title and development into the
availability sheet
availabilityWorksheet.Cells(availabilityWriteRow,
2).Value = statusWorksheet.Cells(staffReadRow, 3).Value
availabilityWorksheet.Cells(availabilityWriteRow,
3).Value = statusWorksheet.Cells(staffReadRow, 5).Value
availabilityWorksheet.Cells(availabilityWriteRow,
4).Value = statusWorksheet.Cells(staffReadRow, 2).Value
'loop through number of weeks to display and put 100%
in each of them
For dateColumnCounter = 3 To weeksToProcess + 2
availabilityWorksheet.Cells(availabilityWriteRow,
dateColumnCounter + 2) = 1
Next
availabilityWriteRow = availabilityWriteRow + 1
End If
End If
staffReadRow = staffReadRow + 1
Loop
=============================== sample code
=============================================
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"CakePHP" group.
To post to this group, send email to cake-php@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/cake-php?hl=en
-~----------~----~----~----~------~----~------~--~---
