Now adding the rest of employee functions, we have some modifications in employee.php
employee.php
<?php
$page_security = 'SA_EMPL';
$path_to_root = '../..';
include_once($path_to_root . "/includes/session.inc");
add_access_extensions();
$js = "";
if ($SysPrefs->use_popup_windows)
$js .= get_js_open_window(900, 500);
if (user_use_date_picker())
$js .= get_js_date_picker();
include_once($path_to_root . "/includes/ui.inc");
include_once($path_to_root . "/modules/payroll/includes/payroll_db.inc");
include_once($path_to_root . "/modules/payroll/includes/payroll_ui.inc");
//----------------------------------------------------------------------------------------
function can_process(){
if (strlen($_POST['EmpFirstName']) == 0){
display_error(_("The employee first name must be entered."));
set_focus('EmpFirstName');
return false;
}
if (strlen($_POST['EmpLastName']) == 0){
display_error(_("The employee Last name must be entered."));
set_focus('EmpLastName');
return false;
}
if (strlen($_POST['EmpAddress']) == 0){
display_error(_("The employee address must be entered."));
set_focus('EmpAddress');
return false;
}
if (!is_date($_POST['EmpHireDate'])){
display_error( _("The date entered is in an invalid format."));
set_focus('EmpHireDate');
return false;
}
if (get_post('EmpInactive') == 1){
if (!is_date($_POST['EmpReleaseDate'])){
display_error( _("A valid release date must be entered for inactive employees."));
set_focus('EmpReleaseDate');
return false;
}
}
return true;
}
//----------------------------------------------------------------------------------------
function handle_submit(&$selected_id)
{
global $Ajax;
if (!can_process())
return;
if ($selected_id) {
update_employee(
$_POST['EmpFirstName'], $_POST['EmpLastName'], $_POST['EmpAddress'], $_POST['EmpPhone'], $_POST['EmpEmail'], $_POST['EmpBirthDate'], $_POST['EmpNotes'], $_POST['EmpHireDate'], $_POST['Department'],$_POST['SalaryScale'], get_post('EmpInactive') ? $_POST['EmpReleaseDate'] : null, get_post('EmpInactive'), $selected_id
);
$Ajax->activate('EmpId');
display_notification(_("Employee details has been updated."));
}
else
{
begin_transaction();
add_employee(
$_POST['EmpFirstName'],
$_POST['EmpLastName'],
$_POST['EmpAddress'],
$_POST['EmpPhone'],
$_POST['EmpEmail'],
$_POST['EmpBirthDate'],
$_POST['EmpNotes'],
$_POST['EmpHireDate'],
$_POST['Department'],
$_POST['SalaryScale']
);
$selected_id = $_POST['EmpId'] = db_insert_id();
commit_transaction();
display_notification(_("A new employee has been added."));
$Ajax->activate('_page_body');
}
}
//----------------------------------------------------------------------------------------
function employee_settings($selected_id){
if (!$selected_id) {
$_POST['EmpFirstName'] =
$_POST['EmpLastName'] =
$_POST['EmpAddress'] =
$_POST['EmpPhone'] =
$_POST['EmpEmail'] =
$_POST['EmpNotes'] = '';
$_POST['EmpBirthDate'] = '01/01/1985';
$_POST['EmpHireDate'] = '01/01/2005';
}
else {
$myrow = get_employee($selected_id);
$_POST['EmpFirstName'] = $myrow["emp_first_name"];
$_POST['EmpLastName'] = $myrow["emp_last_name"];
$_POST['EmpAddress'] = $myrow["emp_address"];
$_POST['EmpPhone'] = $myrow["emp_phone"];
$_POST['EmpEmail'] = $myrow["emp_email"];
$_POST['EmpBirthDate'] = sql2date($myrow["emp_birthdate"]);
$_POST['EmpNotes'] = $myrow["emp_notes"];
$_POST['EmpHireDate'] = sql2date($myrow["emp_hiredate"]);
$_POST['Department'] = $myrow["department_id"];
$_POST['SalaryScale'] = $myrow["salary_scale_id"];
$_POST['EmpReleaseDate'] = sql2date($myrow["emp_releasedate"]);
$_POST['EmpInactive'] = $myrow["inactive"];
}
div_start('emp_info');
start_outer_table(TABLESTYLE2);
table_section(1);
table_section_title(_("General"));
text_row(_("First Name:"), 'EmpFirstName', $_POST['EmpFirstName'], 37, 50);
text_row(_("Last Name:"), 'EmpLastName', $_POST['EmpLastName'], 37, 50);
textarea_row(_("Address:"), 'EmpAddress', $_POST['EmpAddress'], 35, 5);
text_row(_("Phone:"), 'EmpPhone', $_POST['EmpPhone'], 37, 30);
email_row(_("e-Mail:"), 'EmpEmail', $_POST['EmpEmail'], 37, 100);
date_row(_("Birth Date:"), 'EmpBirthDate');
textarea_row(_("Notes:"), 'EmpNotes', null, 35, 5);
table_section(2);
table_section_title(_("Job Information"));
table_section_title(_(" "));
date_row(_("Hire Date:"), 'EmpHireDate');
if($selected_id){
department_list_row(_("Department:"),'Department',$_POST['Department'],false);
salary_scale_list_row(_("Salary scale:"),'SalaryScale',$_POST['SalaryScale'],false);
date_row(_("Release Date:"), 'EmpReleaseDate');
check_row('Inactive:', 'EmpInactive');
}
else {
department_list_row(_("Department:"),'Department','',false);
salary_scale_list_row(_("Salary scale:"),'SalaryScale','',false);
}
end_outer_table(1);
div_end();
div_start('controls');
if (!$selected_id)
submit_center('submit', _("Add new employee"), true, '', 'default');
else
submit_center('submit', _("Update employee"), _('Update employee data'), @$_REQUEST['popup'] ? true : 'default');
div_end();
}
//----------------------------------------------------------------------------------------
page(_($help_context = "Manage Employees"), '', false, "", $js);
start_form();
$selected_id = get_post('EmpId','');
if (db_has_employees()){
start_table(TABLESTYLE_NOBORDER);
start_row();
employee_list_cells(_("Select an employee: "), 'EmpId', null,
_('New employee'), true, check_value('show_inactive'));
check_cells(_("Show inactive:"), 'show_inactive', null, true);
end_row();
end_table();
if (get_post('_show_inactive_update')) {
$Ajax->activate('EmpId');
set_focus('EmpId');
}
} else {
hidden('EmpId');
}
if (isset($_POST['submit']))
handle_submit($selected_id);
employee_settings($selected_id);
end_form();
end_page();
?>
sql/update.sql
-- Table structure for table `0_departments`
CREATE TABLE IF NOT EXISTS `0_departments` (
`dept_id` int(11) NOT NULL AUTO_INCREMENT,
`dept_name` text NOT NULL,
`inactive` tinyint(4) NOT NULL DEFAULT '0',
PRIMARY KEY (`dept_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;
-- Dumbing data into table `0_departments`
INSERT INTO `0_departments` VALUES
(1,'Production',0),
(2,'R&D',0),
(3,'Sales',0);
-- -----------------------------------------------------------------------------
-- Table structure for table `0_employees`
CREATE TABLE IF NOT EXISTS `0_employees` (
`emp_id` int(11) NOT NULL AUTO_INCREMENT,
`emp_first_name` varchar(100) DEFAULT NULL,
`emp_last_name` varchar(100) DEFAULT NULL,
`emp_address` tinytext,
`emp_phone` varchar(30) DEFAULT NULL,
`emp_email` varchar(100) DEFAULT NULL,
`emp_birthdate` date NOT NULL,
`emp_notes` tinytext NOT NULL,
`emp_hiredate` date DEFAULT NULL,
`department_id` int(11) NOT NULL,
`salary_scale_id` int(11) NOT NULL,
`emp_releasedate` date DEFAULT NULL,
`inactive` tinyint(1) NOT NULL DEFAULT '0',
PRIMARY KEY (`emp_id`),
KEY `salary_scale_id` (`salary_scale_id`),
KEY `department_id` (`department_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;
-- -----------------------------------------------------------------------------
-- Table structure for table `0_salaryscales`
CREATE TABLE IF NOT EXISTS `0_salaryscales` (
`salary_scale_id` int(11) NOT NULL AUTO_INCREMENT,
`salary_scale_name` text NOT NULL,
`inactive` tinyint(4) NOT NULL DEFAULT '0',
PRIMARY KEY (`salary_scale_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;
-- Dumbing data into table `0_departments`
INSERT INTO `0_salaryscales` VALUES
(1,'Salary 1',0),
(2,'Salary 2',0),
(3,'Salary 3',0);
-- -----------------------------------------------------------------------------
includes/payroll_db.inc
<?php
include_once($path_to_root."/modules/payroll/includes/db/employee_db.inc");
includes/payroll_ui.inc
<?php
include_once($path_to_root."/modules/payroll/includes/ui/employee_ui.inc");
include_once($path_to_root."/modules/payroll/includes/ui/salary_scale_ui.inc");
include_once($path_to_root."/modules/payroll/includes/ui/departments_ui.inc");
includes/db/employee_db.inc
<?php
function db_has_employees(){
return check_empty_result("SELECT COUNT(*) FROM ".TB_PREF."employees");
}
//----------------------------------------------------------------------------------------
function add_employee($firstname, $lastname, $address, $phone, $email, $bdate, $notes, $hire_date, $department, $salaryscale){
$sql = "INSERT INTO ".TB_PREF."employees (emp_first_name, emp_last_name, emp_address, emp_phone, emp_email, emp_birthdate, emp_notes, emp_hiredate, department_id,salary_scale_id) VALUES ("
.db_escape($firstname).", "
.db_escape($lastname).", "
.db_escape($address).", "
.db_escape($phone).", "
.db_escape($email).", '"
.date2sql($bdate)."', "
.db_escape($notes).", '"
.date2sql($hire_date)."', "
.db_escape($department).", "
.db_escape($salaryscale).")";
db_query($sql,"The employee could not be added");
}
//----------------------------------------------------------------------------------------
function get_employee($employee_id)
{
$sql = "SELECT * FROM ".TB_PREF."employees WHERE emp_id=".db_escape($employee_id);
$result = db_query($sql, "could not get employee");
return db_fetch($result);
}
//----------------------------------------------------------------------------------------
function update_employee($firstname, $lastname, $address, $phone, $email, $bdate, $notes, $hire_date, $department, $salaryscale, $release_date, $inactive, $selected_id){
$sql = "UPDATE ".TB_PREF."employees SET
emp_first_name=".db_escape($firstname).",
emp_last_name=".db_escape($lastname).",
emp_address=".db_escape($address).",
emp_phone=".db_escape($phone).",
emp_email=".db_escape($email).",
emp_birthdate='".date2sql($bdate)."',
emp_notes=".db_escape($notes).",
emp_hiredate='".date2sql($hire_date)."',
department_id = ".db_escape($department).",
salary_scale_id = ".db_escape($salaryscale).",
emp_releasedate='".date2sql($release_date)."',
inactive=".db_escape($inactive)."
WHERE emp_id=".db_escape($selected_id);
db_query($sql,"The employee could not be updated");
}
//----------------------------------------------------------------------------------------
includes/ui/employee_ui.inc
<?php
function employee_list($name, $selected_id=null, $spec_option=false, $submit_on_change=false,
$show_inactive=false, $editkey = false)
{
global $all_items;
$sql = "SELECT emp_id, CONCAT_WS(' ', emp_first_name, emp_last_name), inactive FROM ".TB_PREF."employees ";
$mode = 0;
if ($editkey)
set_editor('employee', $name, $editkey);
$ret = combo_input($name, $selected_id, $sql, 'emp_id', 'emp_first_name',
array(
'order' => array('emp_first_name'),
'search_box' => $mode!=0,
'type' => 1,
'size' => 20,
'spec_option' => $spec_option === true ? _("All Employees") : $spec_option,
'spec_id' => $all_items,
'select_submit'=> $submit_on_change,
'async' => false,
'sel_hint' => $mode ? _('Press Space tab to filter by name fragment; F2 - entry new employee') :
_('Select employee'),
'show_inactive' => $show_inactive
) );
if ($editkey)
$ret .= add_edit_combo('employee');
return $ret;
}
//--------------------------------------------------------------------------------------------
function employee_list_cells($label, $name, $selected_id=null, $all_option=false,
$submit_on_change=false, $show_inactive=false, $editkey = false)
{
if ($label != null)
echo "<td>$label</td>\n";
echo "<td nowrap>";
echo employee_list($name, $selected_id, $all_option, $submit_on_change,
$show_inactive, $editkey);
echo "</td>\n";
}
function employee_list_row($label, $name, $selected_id=null, $all_option = false,
$submit_on_change=false, $show_inactive=false, $editkey = false)
{
echo "<tr><td class='label'>$label</td><td>";
echo employee_list($name, $selected_id, $all_option, $submit_on_change,
$show_inactive, $editkey);
echo "</td></tr>\n";
}
function employee_name($first_name,$last_name)
{
$sql = "SELECT emp_first_name,emp_last_name FROM ".TB_PREF."employees ";
$ret =combo_input($sql,'emp_first_name','emp_last_name');
return $ret;
}
?>
includes/ui/departments_ui.inc
<?php
function department_list($name, $selected_id=null, $spec_option=false, $submit_on_change=false,
$show_inactive=false, $editkey = false)
{
global $all_items;
$sql = "SELECT dept_id,dept_name FROM ".TB_PREF."departments ";
$mode = get_company_pref('no_department_list');
if ($editkey)
set_editor('department', $name, $editkey);
$ret = combo_input($name, $selected_id, $sql, 'dept_id', 'dept_name',
array(
'order' => array('dept_name'),
'search_box' => $mode!=0,
'type' => 1,
'size' => 20,
'search' => array("dept_name","name","tax_id"),
'spec_option' => $spec_option === true ? _("All departments") : $spec_option,
'spec_id' => $all_items,
'select_submit'=> $submit_on_change,
'async' => false,
'sel_hint' => $mode ? _('Press Space tab to filter by name fragment; F2 - entry new department') :
_('Select department'),
'show_inactive' => $show_inactive
) );
if ($editkey)
$ret .= add_edit_combo('department');
return $ret;
}
function department_list_cells($label, $name, $selected_id=null, $all_option=false,
$submit_on_change=false, $show_inactive=false, $editkey = false)
{
if ($label != null)
echo "<td>$label</td>\n";
echo "<td nowrap>";
echo department_list($name, $selected_id, $all_option, $submit_on_change,
$show_inactive, $editkey);
echo "</td>\n";
}
function department_list_row($label, $name, $selected_id=null, $all_option=false,
$submit_on_change=false, $show_inactive=false, $editkey = false){
echo "<tr>";
department_list_cells($label, $name, $selected_id, $all_option,
$submit_on_change, $show_inactive, $editkey);
echo "</tr>";
}
?>
includes/ui/salary_scale_ui.inc
<?php
function salary_scale_list($name, $selected_id=null, $spec_option=false, $submit_on_change=false,
$show_inactive=false, $editkey = false)
{
global $all_items;
$sql = "SELECT salary_scale_id,salary_scale_name FROM ".TB_PREF."salaryscales ";
$mode = get_company_pref('no_leave_list');
if ($editkey)
set_editor('salary_scale', $name, $editkey);
$ret = combo_input($name, $selected_id, $sql, 'salary_scale_id', 'salary_scale_name',
array(
'order' => array('salary_scale_name'),
'search_box' => $mode!=0,
'type' => 1,
'size' => 20,
'search' => array("salary_scale_name"),
'spec_option' => $spec_option === true ? _("All scales") : $spec_option,
'spec_id' => $all_items,
'select_submit'=> $submit_on_change,
'async' => false,
'sel_hint' => $mode ? _('Press Space tab to filter by name fragment; F2 - entry new salary scale') :
_('Select scale'),
'show_inactive' => $show_inactive
) );
if ($editkey)
$ret .= add_edit_combo('leave');
return $ret;
}
function salary_scale_list_cells($label, $name, $selected_id=null, $all_option=false,
$submit_on_change=false, $show_inactive=false, $editkey = false)
{
if ($label != null)
echo "<td>$label</td>\n";
echo "<td nowrap>";
echo salary_scale_list($name, $selected_id, $all_option, $submit_on_change,
$show_inactive, $editkey);
echo "</td>\n";
}
function salary_scale_list_row($label, $name, $selected_id=null, $all_option=false,
$submit_on_change=false, $show_inactive=false, $editkey = false)
{
echo "<tr>";
salary_scale_list_cells($label, $name, $selected_id, $all_option,
$submit_on_change, $show_inactive, $editkey);
echo "</tr>";
}