protected function DoBeforeCreate()
{
$selectQuery = 'SELECT DISTINCT
`task`.`assigned_to`,
SUM(`task`.`hrs`) AS `total_hrs`,
`staff`.`username`,`staff`.`staff_id`
FROM
`staff`,
`task`
WHERE
`staff`.`staff_id` = `task`.`assigned_to`
GROUP BY
`staff`.`username`';
$insertQuery = array();
$updateQuery = array();
$deleteQuery = array();
$this->dataset = new QueryDataset(new MyConnectionFactory(), GetConnectionOptions(), $selectQuery, $insertQuery, $updateQuery, $deleteQuery, 'total allocated hours small');
$field = new StringField('assigned_to');
$this->dataset->AddField($field, false);
$field = new StringField('total_hrs');
$this->dataset->AddField($field, false);
$field = new StringField('username');
$field->SetIsNotNull(true);
$this->dataset->AddField($field, false);
$field = new IntegerField('staff_id');
if (!$this->GetSecurityInfo()->AdminGrant()) {
$field->SetReadOnly(true, GetApplication()->GetCurrentUserId());
}
$field->SetIsNotNull(true);
$this->dataset->AddField($field, true);
}
protected function DoBeforeCreate()
{
$selectQuery = 'SELECT `timesheet`.`name`,`timesheet`.`staff_id`,
SUM(`timesheet`.`hours`) AS `total_hours`,
`project`.`project_name`,
`task`.`task_name`,
SUM(`task`.`hrs`) AS `assigned_hours`
FROM
`task`
INNER JOIN `project` ON (`task`.`project_id` = `project`.`project_id`)
INNER JOIN `timesheet` ON (`task`.`task_id` = `timesheet`.`task_id`)
GROUP BY
`timesheet`.`name`,`project`.`project_name`';
$insertQuery = array();
$updateQuery = array();
$deleteQuery = array();
$this->dataset = new QueryDataset(new MyConnectionFactory(), GetConnectionOptions(), $selectQuery, $insertQuery, $updateQuery, $deleteQuery, 'Hours per Project by user');
$field = new StringField('name');
$this->dataset->AddField($field, false);
$field = new IntegerField('staff_id');
if (!$this->GetSecurityInfo()->AdminGrant()) {
$field->SetReadOnly(true, GetApplication()->GetCurrentUserId());
}
$field->SetIsNotNull(true);
$this->dataset->AddField($field, true);
$field = new IntegerField('total_hours');
$this->dataset->AddField($field, false);
$field = new StringField('project_name');
$this->dataset->AddField($field, false);
$field = new StringField('task_name');
$this->dataset->AddField($field, false);
$field = new StringField('assigned_hours');
$this->dataset->AddField($field, false);
}
protected function DoBeforeCreate()
{
$selectQuery = 'SELECT `x`.*
, SUM(`task`.`hrs`)AS `assigned_hrs`
FROM
( SELECT `staff_id`
, `name`
, SUM(`hours`) AS `actual_hours`
FROM `timesheet`
GROUP
BY `timesheet`.`staff_id`
) `x`
JOIN `task`
ON `assigned_to` = `x`.`staff_id`
GROUP
BY `staff_id`';
$insertQuery = array();
$updateQuery = array();
$deleteQuery = array();
$this->dataset = new QueryDataset(new MyConnectionFactory(), GetConnectionOptions(), $selectQuery, $insertQuery, $updateQuery, $deleteQuery, 'Total hrs Estimated Hrs');
$field = new IntegerField('staff_id');
if (!$this->GetSecurityInfo()->AdminGrant()) {
$field->SetReadOnly(true, GetApplication()->GetCurrentUserId());
}
$field->SetIsNotNull(true);
$this->dataset->AddField($field, true);
$field = new StringField('name');
$this->dataset->AddField($field, false);
$field = new IntegerField('actual_hours');
$this->dataset->AddField($field, true);
$field = new StringField('assigned_hrs');
$this->dataset->AddField($field, true);
}
protected function DoBeforeCreate()
{
$this->dataset = new TableDataset(new MyConnectionFactory(), GetConnectionOptions(), '`staff`');
$field = new IntegerField('staff_id', null, null, true);
if (!$this->GetSecurityInfo()->AdminGrant()) {
$field->SetReadOnly(true, GetApplication()->GetCurrentUserId());
}
$field->SetIsNotNull(true);
$this->dataset->AddField($field, true);
$field = new StringField('username');
$field->SetIsNotNull(true);
$this->dataset->AddField($field, false);
$field = new StringField('email');
$field->SetIsNotNull(true);
$this->dataset->AddField($field, false);
$field = new StringField('picture');
$field->SetIsNotNull(true);
$this->dataset->AddField($field, false);
$field = new StringField('password');
$field->SetIsNotNull(true);
$this->dataset->AddField($field, false);
$field = new IntegerField('dept_id');
$field->SetIsNotNull(true);
$this->dataset->AddField($field, false);
$field = new StringField('position');
$field->SetIsNotNull(true);
$this->dataset->AddField($field, false);
$field = new StringField('emp_number');
$field->SetIsNotNull(true);
$this->dataset->AddField($field, false);
$this->dataset->AddLookupField('dept_id', 'department', new IntegerField('DEPT_ID', null, null, true), new StringField('DEPARTMENT', 'dept_id_DEPARTMENT', 'dept_id_DEPARTMENT_department'), 'dept_id_DEPARTMENT_department');
}
protected function DoBeforeCreate()
{
$this->dataset = new TableDataset(new MyConnectionFactory(), GetConnectionOptions(), '`timesheet`');
$field = new IntegerField('timesheet_id', null, null, true);
$field->SetIsNotNull(true);
$this->dataset->AddField($field, true);
$field = new StringField('name');
$this->dataset->AddField($field, false);
$field = new IntegerField('task_id');
$this->dataset->AddField($field, false);
$field = new StringField('hours');
$this->dataset->AddField($field, false);
$field = new StringField('notes');
$field->SetIsNotNull(true);
$this->dataset->AddField($field, false);
$field = new StringField('time_type');
$this->dataset->AddField($field, false);
$field = new DateField('date');
$field->SetIsNotNull(true);
$this->dataset->AddField($field, false);
$field = new IntegerField('staff_id');
if (!$this->GetSecurityInfo()->AdminGrant()) {
$field->SetReadOnly(true, GetApplication()->GetCurrentUserId());
}
$field->SetIsNotNull(true);
$this->dataset->AddField($field, false);
$this->dataset->AddLookupField('task_id', 'task', new IntegerField('task_id', null, null, true), new StringField('task_name', 'task_id_task_name', 'task_id_task_name_task'), 'task_id_task_name_task');
}
protected function DoBeforeCreate()
{
$selectQuery = 'SELECT task.task_name,
project.project_name,
timesheet.name,
timesheet.date,
timesheet.hours,
timesheet.staff_id,
pay_period.start_date,
pay_period.end_date,
pay_period.pay_period_number,
pay_period.YEAR,
staff.position,
staff.emp_number
FROM timesheet
LEFT JOIN task ON timesheet.task_id = task.task_id
LEFT JOIN project ON task.project_id = project.project_id
LEFT JOIN staff ON timesheet.staff_id = staff.staff_id
LEFT JOIN pay_period ON timesheet.date BETWEEN pay_period.start_date AND pay_period.end_date';
$insertQuery = array();
$updateQuery = array();
$deleteQuery = array();
$this->dataset = new QueryDataset(new MyConnectionFactory(), GetConnectionOptions(), $selectQuery, $insertQuery, $updateQuery, $deleteQuery, 'View Pay Period');
$field = new StringField('task_name');
$this->dataset->AddField($field, false);
$field = new StringField('project_name');
$this->dataset->AddField($field, false);
$field = new StringField('name');
$this->dataset->AddField($field, false);
$field = new DateField('date');
$field->SetIsNotNull(true);
$this->dataset->AddField($field, true);
$field = new StringField('hours');
$this->dataset->AddField($field, true);
$field = new IntegerField('staff_id');
if (!$this->GetSecurityInfo()->AdminGrant()) {
$field->SetReadOnly(true, GetApplication()->GetCurrentUserId());
}
$field->SetIsNotNull(true);
$this->dataset->AddField($field, true);
$field = new DateTimeField('start_date');
$field->SetIsNotNull(true);
$this->dataset->AddField($field, true);
$field = new DateTimeField('end_date');
$field->SetIsNotNull(true);
$this->dataset->AddField($field, true);
$field = new IntegerField('pay_period_number');
$field->SetIsNotNull(true);
$this->dataset->AddField($field, true);
$field = new IntegerField('YEAR');
$field->SetIsNotNull(true);
$this->dataset->AddField($field, true);
$field = new StringField('position');
$field->SetIsNotNull(true);
$this->dataset->AddField($field, true);
$field = new StringField('emp_number');
$field->SetIsNotNull(true);
$this->dataset->AddField($field, true);
}
protected function DoBeforeCreate()
{
$this->dataset = new TableDataset(new MyConnectionFactory(), GetConnectionOptions(), '`project`');
$field = new IntegerField('project_id', null, null, true);
$field->SetIsNotNull(true);
$this->dataset->AddField($field, true);
$field = new IntegerField('programm_id');
$this->dataset->AddField($field, false);
$field = new StringField('project_name');
$this->dataset->AddField($field, false);
$field = new DateField('date_start');
$this->dataset->AddField($field, false);
$field = new DateField('date_end');
$this->dataset->AddField($field, false);
$field = new IntegerField('approved_budget');
$this->dataset->AddField($field, false);
$field = new StringField('notes');
$field->SetIsNotNull(true);
$this->dataset->AddField($field, false);
$field = new StringField('priority');
$this->dataset->AddField($field, false);
$field = new StringField('lead');
$this->dataset->AddField($field, false);
$field = new StringField('level_of_service');
$field->SetIsNotNull(true);
$this->dataset->AddField($field, false);
$field = new StringField('multi_year');
$field->SetIsNotNull(true);
$this->dataset->AddField($field, false);
$field = new StringField('public_engagement');
$field->SetIsNotNull(true);
$this->dataset->AddField($field, false);
$field = new StringField('year');
$field->SetIsNotNull(true);
$this->dataset->AddField($field, false);
$field = new StringField('approved');
$field->SetIsNotNull(true);
$this->dataset->AddField($field, false);
$field = new StringField('progress');
$field->SetIsNotNull(true);
$this->dataset->AddField($field, false);
$field = new IntegerField('staff_id');
if (!$this->GetSecurityInfo()->AdminGrant()) {
$field->SetReadOnly(false, GetApplication()->GetCurrentUserId());
}
$field->SetIsNotNull(true);
$this->dataset->AddField($field, false);
$field = new IntegerField('dept_id');
$field->SetIsNotNull(true);
$this->dataset->AddField($field, false);
$field = new IntegerField('budget_spent');
$field->SetIsNotNull(true);
$this->dataset->AddField($field, false);
$field = new IntegerField('admin_flag');
$field->SetIsNotNull(true);
$this->dataset->AddField($field, false);
$this->dataset->AddLookupField('programm_id', 'program', new IntegerField('program_id', null, null, true), new StringField('program_name', 'programm_id_program_name', 'programm_id_program_name_program'), 'programm_id_program_name_program');
$this->dataset->AddLookupField('priority', 'Priority', new StringField('Name'), new StringField('Name', 'priority_Name', 'priority_Name_Priority'), 'priority_Name_Priority');
$this->dataset->AddLookupField('lead', 'staff', new IntegerField('staff_id', null, null, true), new StringField('username', 'lead_username', 'lead_username_staff'), 'lead_username_staff');
$this->dataset->AddLookupField('dept_id', 'department', new IntegerField('DEPT_ID', null, null, true), new StringField('DEPARTMENT', 'dept_id_DEPARTMENT', 'dept_id_DEPARTMENT_department'), 'dept_id_DEPARTMENT_department');
}
protected function DoBeforeCreate()
{
$selectQuery = 'SELECT
project.*,
staff.staff_id AS STAFFID
FROM project,
staff
WHERE project.dept_id = staff.dept_id';
$insertQuery = array('INSERT INTO `project` (
`programm_id`,
`project_name`,
`date_start`,
`date_end`,
`approved_budget`,
`notes`,
`priority`,
`lead`,
`level_of_service`,
`multi_year`,
`public_engagement`,
`year`,
`approved`,
`progress`,
`staff_id`,
`dept_id`,
`budget_spent`
)
VALUES (
:programm_id,
:project_name,
:date_start,
:date_end,
:approved_budget,
:notes,
:priority,
:lead,
:level_of_service,
:multi_year,
:public_engagement,
:year,
:approved,
:progress,
:staff_id,
:dept_id,
:budget_spent
)');
$updateQuery = array('UPDATE
`project`
SET
`programm_id` = :programm_id,
`project_name` = :project_name,
`date_start` = :date_start,
`date_end` = :date_end,
`approved_budget` = :approved_budget,
`notes` = :notes,
`priority` = :priority,
`lead` = :lead,
`level_of_service` = :level_of_service,
`multi_year` = :multi_year,
`public_engagement` = :public_engagement,
`year` = :year,
`approved` = :approved,
`progress` = :progress,
`staff_id` = :staff_id,
`dept_id` = :dept_id,
`budget_spent` = :budget_spent
WHERE
`project_id` = :OLD_project_id');
$deleteQuery = array('DELETE FROM `project`
WHERE
`project_id` = :project_id');
$this->dataset = new QueryDataset(new MyConnectionFactory(), GetConnectionOptions(), $selectQuery, $insertQuery, $updateQuery, $deleteQuery, 'department_projects');
$field = new IntegerField('project_id');
$field->SetIsNotNull(true);
$this->dataset->AddField($field, true);
$field = new IntegerField('programm_id');
$this->dataset->AddField($field, false);
$field = new StringField('project_name');
$this->dataset->AddField($field, false);
$field = new DateField('date_start');
$this->dataset->AddField($field, false);
$field = new DateField('date_end');
$this->dataset->AddField($field, false);
$field = new IntegerField('approved_budget');
$this->dataset->AddField($field, false);
$field = new StringField('notes');
$field->SetIsNotNull(true);
$this->dataset->AddField($field, false);
$field = new StringField('priority');
$this->dataset->AddField($field, false);
$field = new StringField('lead');
$this->dataset->AddField($field, false);
$field = new StringField('level_of_service');
$field->SetIsNotNull(true);
$this->dataset->AddField($field, false);
$field = new StringField('multi_year');
$field->SetIsNotNull(true);
$this->dataset->AddField($field, false);
$field = new StringField('public_engagement');
$field->SetIsNotNull(true);
$this->dataset->AddField($field, false);
$field = new StringField('year');
$field->SetIsNotNull(true);
$this->dataset->AddField($field, false);
$field = new StringField('approved');
$field->SetIsNotNull(true);
$this->dataset->AddField($field, false);
$field = new StringField('progress');
$field->SetIsNotNull(true);
$this->dataset->AddField($field, false);
$field = new IntegerField('staff_id');
if (!$this->GetSecurityInfo()->AdminGrant()) {
$field->SetReadOnly(true, GetApplication()->GetCurrentUserId());
}
$field->SetIsNotNull(true);
$this->dataset->AddField($field, false);
$field = new IntegerField('dept_id');
$field->SetIsNotNull(true);
$this->dataset->AddField($field, false);
$field = new IntegerField('budget_spent');
$field->SetIsNotNull(true);
$this->dataset->AddField($field, false);
$field = new IntegerField('admin_flag');
$field->SetIsNotNull(true);
$this->dataset->AddField($field, false);
$field = new IntegerField('STAFFID');
$this->dataset->AddField($field, false);
$this->dataset->AddCustomCondition(EnvVariablesUtils::EvaluateVariableTemplate($this->GetColumnVariableContainer(), 'STAFFID = %CURRENT_USER_ID% OR %CURRENT_USER_ID% = 1'));
}
protected function DoBeforeCreate()
{
$this->dataset = new TableDataset(new MyConnectionFactory(), GetConnectionOptions(), '`task`');
$field = new IntegerField('task_id', null, null, true);
$field->SetIsNotNull(true);
$this->dataset->AddField($field, true);
$field = new IntegerField('project_id');
$this->dataset->AddField($field, false);
$field = new StringField('assigned_to');
$this->dataset->AddField($field, false);
$field = new DateField('date_start');
$this->dataset->AddField($field, false);
$field = new DateField('date_end');
$this->dataset->AddField($field, false);
$field = new StringField('task_name');
$this->dataset->AddField($field, false);
$field = new StringField('notes');
$field->SetIsNotNull(true);
$this->dataset->AddField($field, false);
$field = new IntegerField('hrs');
$this->dataset->AddField($field, false);
$field = new StringField('wo');
$this->dataset->AddField($field, false);
$field = new IntegerField('staff_id');
if (!$this->GetSecurityInfo()->AdminGrant()) {
$field->SetReadOnly(true, GetApplication()->GetCurrentUserId());
}
$this->dataset->AddField($field, false);
}
protected function DoBeforeCreate()
{
$selectQuery = 'SELECT
`timesheet`.`timesheet_id`,
`timesheet`.`name`,
`timesheet`.`task_id`,
SUM(`timesheet`.`hours`) AS `total_hours`,
`timesheet`.`notes`,
`timesheet`.`date`,
`timesheet`.`staff_id`,
`timesheet`.`hours`
FROM
`timesheet`
WHERE
`timesheet`.`date` >= DATE_SUB(CURDATE(), INTERVAL 14 DAY)
GROUP BY
`timesheet`.`timesheet_id`,
`timesheet`.`name`,
`timesheet`.`task_id`,
`timesheet`.`notes`,
`timesheet`.`date`,
`timesheet`.`staff_id`,
`timesheet`.`hours`';
$insertQuery = array();
$updateQuery = array();
$deleteQuery = array();
$this->dataset = new QueryDataset(new MyConnectionFactory(), GetConnectionOptions(), $selectQuery, $insertQuery, $updateQuery, $deleteQuery, 'Pay Period');
$field = new IntegerField('timesheet_id');
$field->SetIsNotNull(true);
$this->dataset->AddField($field, true);
$field = new StringField('name');
$this->dataset->AddField($field, false);
$field = new IntegerField('task_id');
$this->dataset->AddField($field, false);
$field = new IntegerField('total_hours');
$this->dataset->AddField($field, false);
$field = new StringField('notes');
$field->SetIsNotNull(true);
$this->dataset->AddField($field, false);
$field = new DateField('date');
$field->SetIsNotNull(true);
$this->dataset->AddField($field, false);
$field = new IntegerField('staff_id');
if (!$this->GetSecurityInfo()->AdminGrant()) {
$field->SetReadOnly(true, GetApplication()->GetCurrentUserId());
}
$field->SetIsNotNull(true);
$this->dataset->AddField($field, false);
$field = new StringField('hours');
$this->dataset->AddField($field, false);
$this->dataset->AddLookupField('task_id', 'task', new IntegerField('task_id', null, null, true), new StringField('task_name', 'task_id_task_name', 'task_id_task_name_task'), 'task_id_task_name_task');
}
