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'); }