1 (edited by notrinos 12/27/2016 06:44:51 am)

Topic: Payroll extension for practice

I have been learning PHP for months and decided to build a simple FA Payroll for learning php and FA framework, all codes will posted this topic.

Phuong

Re: Payroll extension for practice

First thing to do to create an extension of FA is make a folder for the ext in the modules folder, for this payroll we will have /modules/payroll. In this we need two files : hooks.php and index.php for install/active the ext. You can read more about these files here

Phuong

Re: Payroll extension for practice

hooks.php with first employee entry

<?php
define ('SS_PAYROLL', 250<<8);

class payroll_app extends application{
    var $apps;
    function payroll_app() {
        global $path_to_root;
       
        $this->application("payroll", _($this->help_context = "Payroll"));
       
        $this->add_module(_("Transactions"));
        $this->add_lapp_function(0, 'Employee', $path_to_root.'/modules/payroll/employee.php', 'SA_EMPL', MENU_TRANSACTION);
       
        $this->add_extensions();
       
    }     
}

class hooks_payroll extends hooks {
    var $module_name = 'Payroll';
   
    function install_tabs($app) {
        $app->add_application(new payroll_app);
    }
   
    function install_access() {
        $security_sections[SS_PAYROLL] =  _("Payroll");
        $security_areas['SA_EMPL'] = array(SS_PAYROLL|1, _("Employee entry"));

        return array($security_areas, $security_sections);
    }
   
    function activate_extension($company, $check_only=true) {
            global $db_connections;

            $updates = array( 'update.sql' => array('') );
            return $this->update_databases($company, $updates, $check_only);
    }
}

?>

Phuong

4 (edited by notrinos 12/30/2016 06:34:36 am)

Re: Payroll extension for practice

In FA2.4.RC1 , when active your self-build ext you will see a warning :

"Package payroll is incompatible with current application version and cannot be activated. Check Install/Activate page for newer package version".

In this case i commented lines 215-220 in admin/inst_module.php to active my ext. I don't know how modules can be granted in FA2.4.

Phuong

Re: Payroll extension for practice

Now we create employee.php which shows the components to view, add, modify employees. To create components we need to include ui.inc to call functions those that written to create text inputs, date inputs, text areas, buttons, selections, check box...

<?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");

//--------------------------------------------------------------------------------------------

function employee_settings($selected_id){

    if (!$selected_id) {
        $_POST['EmpFirstName'] =
        $_POST['EmpLastName']  =
        $_POST['EmpAddress']   =
        $_POST['EmpPhone']     =
        $_POST['EmpEmail']     =
        $_POST['EmpNotes']     = '';
        $_POST['EmpBirthDate'] = Today();
    }
    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"];
    }

    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"));

    // Here will continue.......

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

//--------------------------------------------------------------------------------------------

employee_settings($selected_id);

end_form();
end_page();

?>

Phuong

Re: Payroll extension for practice

In order to complete the employee entry we have to take a tour in FA structure. At last codes we created some components but not have any database function yet. When you look in to folder structure of some other extension you'll see files and folder like this:

modules/

├── payroll/
     │
     ├── employee.php
     ├── hooks.php
     ├── index.php
     ├── includes/
     │      │
     │      ├── payroll_db.inc   
     │      ├── payroll_ui.inc
     │      ├── ui/
     │      │     │
     │      │     ├── employee_ui.inc
     │      │     ├──
     │      │
     │      ├── db/
     │             ├── employee_db.inc
     │             ├──
     │
     ├── sql
             ├── update.sql

I have learned it from kvvaradha's simple HRM and sticmann's payroll module

Phuong

Re: Payroll extension for practice

thats nice.

i have few extended version of hrm. you can take a look at my home page.

Kvcodes

8 (edited by notrinos 12/31/2016 11:46:16 am)

Re: Payroll extension for practice

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>";
}
Phuong

Re: Payroll extension for practice

Continue to create department managment we need to add

  • folder named 'manage' in the payroll folder which will contains maintenance functions.

  • department.php in the folder modules/payroll/manage/

  • departments_ui.inc in folder modules/payroll/includes/ui/

  • departments_db.inc in folder modules/payroll/includes/db/

and changing in hooks.php

Phuong

Re: Payroll extension for practice

edited hooks.php The blue texts are added(edited).

<?php
define ('SS_PAYROLL', 250<<8);
class payroll_app extends application{
    var $apps;
    function payroll_app() {
        global $path_to_root;
       
        $this->application("payroll", _($this->help_context = "Payroll"));
       
        $this->add_module(_("Transactions"));
        $this->add_lapp_function(0, 'Employee', $path_to_root.'/modules/payroll/employee.php', 'SA_EMPL', MENU_TRANSACTION);
       
        $this->add_module(_("Inquiries and Reports"));
       
        $this->add_module(_("Maintenance"));
        $this->add_lapp_function(2, 'Department', $path_to_root.'/modules/payroll/manage/department.php', 'SA_PRSETUP', MENU_MAINTENANCE);
        $this->add_lapp_function(2, 'Salary scale', $path_to_root.'/modules/payroll/manage/salary_scale.php', 'SA_PRSETUP', MENU_MAINTENANCE);

        $this->add_extensions();
       
    }     
}
class hooks_payroll extends hooks {
    var $module_name = 'Payroll';
   
    function install_tabs($app) {
        $app->add_application(new payroll_app);
    }
   
    function install_access() {
        $security_sections[SS_PAYROLL] =  _("Payroll");
        $security_areas['SA_EMPL'] = array(SS_PAYROLL|1, _("Employee entry"));
        $security_areas['SA_PRSETUP'] = array(SS_PAYROLL|1, _("Payroll maintenance"));
        return array($security_areas, $security_sections);
    }
   
    function activate_extension($company, $check_only=true) {
            global $db_connections;
            $updates = array( 'update.sql' => array('') );
            return $this->update_databases($company, $updates, $check_only);
    }
}
?>

Phuong

Re: Payroll extension for practice

department.php

<?php

$page_security = 'SA_PRSETUP';
$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();
    
page(_($help_context = "Manage Department"), '', false, '', $js); 

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");
$selected_id = get_post('dept_id','');
//---------------------------------------------------------------------------------------

function can_process(){
    
    if (strlen($_POST['dept_name']) == 0) {
        
        display_error(_("The department name cannot be empty."));
        set_focus('dept_name');
        return false;
    } 

    return true;
}

//--------------------------------------------------------------------------------------------

function handle_submit(&$selected_id) {
    
    global $Ajax;

    if (!can_process())
        return;
        
    if ($selected_id) {
        update_department($_POST['dept_id'], $_POST['dept_name']);        
        $Ajax->activate('dept_id'); 
        display_notification(_("Department has been updated."));
    } 
    else {     

        begin_transaction();
        add_department($_POST['dept_name']);
        $selected_id = $_POST['dept_id'] = db_insert_id();
        commit_transaction();
        display_notification(_("A new department has been added."));        
        $Ajax->activate('_page_body');
    }
}
//---------------------------------------------------------------------------------------

if (isset($_POST['submit'])) {
    
    handle_submit($selected_id);
}
//---------------------------------------------------------------------------------------

if (isset($_POST['delete'])) {
    
    if (department_used($selected_id)){
        display_error(_("Cannot delete this department because employees have been created using it."));

    }
    else {
         $cancel_delete = 0;
         if ($cancel_delete == 0) {
             delete_department($selected_id);
             display_notification(_("Selected department has been deleted."));
             unset($_POST['dept_id']);
             unset($_POST['dept_name']);
             $selected_id = '';
             $Ajax->activate('_page_body');
         }
    }
}

function department_settings($selected_id) {
    
    global $SysPrefs, $path_to_root;
    
    if (!$selected_id) {
        
         if (list_updated('dept_id') || !isset($_POST['dept_name'])) {
            $_POST['dept_name'] = '';
        }
    }
    else {
        $myrow = get_department($selected_id);
        $_POST['dept_name'] = $myrow["dept_name"];
    }

    start_table(TABLESTYLE2);
    text_row(_("Department Name:"), 'dept_name', $_POST['dept_name'], 40, 40);
    end_table(1);

    div_start('controls');
    if (!$selected_id){
        submit_center('submit', _("Add New Department"), true, '', 'default');
    } 
    else {
        
        submit_center_first('submit', _("Update"), _('Update Department data'), @$_REQUEST['popup'] ? true : 'default');
        submit_return('select', $selected_id, _("Select this department and return to document entry."));
        submit_center_last('delete', _("Delete"), _('Delete department data if have been never used'), true);
    }
    div_end();
}

//---------------------------------------------------------------------------------------
 
start_form();

if (db_has_departments()) {
    
    start_table(TABLESTYLE_NOBORDER);
    start_row();
    
    department_list_cells(_("Select a department: "), 'dept_id', null, _('New department'), true, check_value('show_inactive'));
    check_cells(_("Show inactive:"), 'show_inactive', null, true);
    
    end_row();
    end_table(1);    
    
    if (get_post('_show_inactive_update')) {
        $Ajax->activate('dept_id');
        set_focus('dept_id');
    }
} 
else {
    hidden('dept_id');
}

department_settings($selected_id); 

hidden('popup', @$_REQUEST['popup']);
end_form();
end_page();

?>

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>";
}

?> 

department_db.inc

<?php

function db_has_departments(){

    return check_empty_result("SELECT COUNT(*) FROM ".TB_PREF."departments");
}

//---------------------------------------------------------------------------------------

function add_department($name)
{
    $sql = "INSERT INTO ".TB_PREF."departments (dept_name ) VALUES (".db_escape($name).")";

    db_query($sql,"The department could not be added");
}


//---------------------------------------------------------------------------------------

function update_department($id,$name){

    $sql = "UPDATE ".TB_PREF."departments SET dept_name=".db_escape($name)." WHERE dept_id=".db_escape($id);

    db_query($sql,"The department could not be updated");

}

//---------------------------------------------------------------------------------------

function delete_department($dept_id){

    begin_transaction();

    $sql = "DELETE FROM ".TB_PREF."departments WHERE dept_id=".db_escape($dept_id);;
    db_query($sql,"cannot delete department");
    commit_transaction();
}

//--------------------------------------------------------------------------------------

function get_all_department($active=true){   //get only active departments by default

  if ($active == true)
    $act_sql = "WHERE inactive = 0";
  $sql = "SELECT dept_id, dept_name FROM ".TB_PREF."departments $act_sql";
  
  return db_query($sql, "The departments table is inaccessible");

}

//--------------------------------------------------------------------------------------
function get_department($department_id){

    $sql = "SELECT * FROM ".TB_PREF."departments WHERE dept_id=".db_escape($department_id);
    $result = db_query($sql, "could not get department");

    return db_fetch($result);
}

//---------------------------------------------------------------------------------------

function department_used($department) {

    $sql= "SELECT COUNT(*) FROM ".TB_PREF."employees WHERE department_id=".db_escape($department);
    $result = db_query($sql, "could not query employees");
    $myrow = db_fetch_row($result);
    return ($myrow[0] > 0);
}

//---------------------------------------------------------------------------------------

?>
Phuong

Re: Payroll extension for practice

Next post will show salary_scale functions, it similar to department  structure.

Phuong

Re: Payroll extension for practice

An ERD of the new tables (schema) that will be used in the extension should be set out for reference.

Re: Payroll extension for practice

salary_scale.php for add, edit salary scales, place to modules/payroll/manage/

<?php

$page_security = 'SA_PRSETUP';
$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();
    
page(_($help_context = "Manage salary scales"), '', false, "", $js); 

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");

$selected_id = get_post('salary_scale_id','');
//---------------------------------------------------------------------------------------

function can_process() {
    
    if (strlen($_POST['salary_scale_name']) == 0) {
        
        display_error(_("The name field cannot be empty."));
        set_focus('salary_scale_name');
        return false;
    } 
    return true;
}

//---------------------------------------------------------------------------------------

function handle_submit(&$selected_id) {
    global $path_to_root, $Ajax;

    if (!can_process())
        return;
        
    if ($selected_id) {    
            
        update_salary_scale($_POST['salary_scale_id'], $_POST['salary_scale_name']);        
        $Ajax->activate('salary_scale_id'); 
        display_notification(_("Salary scale has been updated."));
    } 
    else {     

        begin_transaction();
        add_salary_scale($_POST['salary_scale_name']);
        $selected_id = $_POST['salary_scale_id'] = db_insert_id();
        commit_transaction();
        display_notification(_("A new salary scale has been added."));        
        $Ajax->activate('_page_body');
    }
}

//---------------------------------------------------------------------------------------

if (isset($_POST['submit'])) {
    
    handle_submit($selected_id);
}

//---------------------------------------------------------------------------------------

if (isset($_POST['delete'])) {
    
    if (salary_scale_used($selected_id)){
        display_error(_("Cannot delete this salary scale because employees have been created using it."));
    }
    else {
         $cancel_delete = 0;
         if ($cancel_delete == 0) {
             delete_salary_scale($selected_id);
             display_notification(_("Selected salary scale has been deleted."));
             unset($_POST['salary_scale_id']);
             unset($_POST['salary_scale_name']);
             $selected_id = '';
             $Ajax->activate('_page_body');
         }
    }
}

//---------------------------------------------------------------------------------------

function salary_scale_settings($selected_id) {
    global $SysPrefs, $path_to_root;
    
    if (!$selected_id) {
        
         if (list_updated('salary_scale_id') || !isset($_POST['salary_scale_name']))
            $_POST['salary_scale_name'] = '';
    }
    else {    
        $myrow = get_salary_scale($selected_id);    
        $_POST['salary_scale_name'] = $myrow["salary_scale_name"];
    }

    start_table(TABLESTYLE2);
    text_row(_("Salary scale name:"), 'salary_scale_name', $_POST['salary_scale_name'], 40, 40);    
    
    end_table(1);

    div_start('controls');
    if (!$selected_id) {
        
        submit_center('submit', _("Add new salary scale"), true, '', 'default');
    } 
    else {
        submit_center_first('submit', _("Update"), _('Update salary scale data'), @$_REQUEST['popup'] ? true : 'default');
        submit_return('select', $selected_id, _("Select this salary scale and return to document entry."));
        submit_center_last('delete', _("Delete"), _('Delete salary scale data if have been never used'), true);
    }
    div_end();
}

//---------------------------------------------------------------------------------------
 
start_form();

if (db_has_salary_scale()) {
    start_table(TABLESTYLE_NOBORDER);
    start_row();
    
    salary_scale_list_cells(_("Select a scale: "), 'salary_scale_id', null, _('New salary scale'), true, check_value('show_inactive'));
    check_cells(_("Show inactive:"), 'show_inactive', null, true);
    
    end_row();
    end_table(1);
    
    if (get_post('_show_inactive_update')) {
        $Ajax->activate('salary_scale_id');
        set_focus('salary_scale_id');
    }
}
else {
    hidden('salary_scale_id');
}

salary_scale_settings($selected_id); 

hidden('popup', @$_REQUEST['popup']);
end_form();
end_page();

?>

salary_scale_ui.inc place to modules/payroll/ui

<?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 salary 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>";
}

//----------------------------------------------------------------------------------------------

salary_scale_db.inc place to modules/payroll/db

<?php

function add_salary_scale($name) {

    $sql = "INSERT INTO ".TB_PREF."salaryscales (salary_scale_name ) VALUES (".db_escape($name).")";
    
    db_query($sql,"The scale could not be added");
}

//---------------------------------------------------------------------------------------

function update_salary_scale($id,$name) {

    $sql = "UPDATE ".TB_PREF."salaryscales SET salary_scale_name=".db_escape($name)." WHERE salary_scale_id=".db_escape($id);

    db_query($sql,"The scale could not be updated");

}

//---------------------------------------------------------------------------------------

function delete_salary_scale($scale_id) {
    
    $sql = "DELETE FROM ".TB_PREF."salaryscales WHERE salary_scale_id=".db_escape($scale_id);;
    db_query($sql,"cannot delete salary scale");
    commit_transaction();
}

//---------------------------------------------------------------------------------------

function get_all_scale($active=true) { //get only active scale by default
    if ($active == true)
    $act_sql = "WHERE inactive = 0";
    $sql = "SELECT salary_scale_id, salary_scale_name FROM ".TB_PREF."salaryscales $act_sql";
  
    return db_query($sql, "The salaryscales table is inaccessible");

}

//---------------------------------------------------------------------------------------

function get_salary_scale($scale_id) {

    $sql = "SELECT * FROM ".TB_PREF."salaryscales WHERE salary_scale_id=".db_escape($scale_id);
    $result = db_query($sql, "could not get salaryscales");

    return db_fetch($result);
}

//--------------------------------------------------------------------------------------

function db_has_salary_scale() {

    return check_empty_result("SELECT COUNT(*) FROM ".TB_PREF."salaryscales");
}

//--------------------------------------------------------------------------------------

function salary_scale_used($scale) {

    $sql= "SELECT COUNT(*) FROM ".TB_PREF."employees WHERE salary_scale_id=".db_escape($scale);
    $result = db_query($sql, "could not query employees");
    $myrow = db_fetch_row($result);
    return ($myrow[0] > 0);
}


?>

after create these files remember to include them in payroll_ui.inc and payroll_db.inc

Phuong

Re: Payroll extension for practice

Now we add two functions :

  • Payroll rules which assigns gl accounts for salary scales

  • Salary structure which specifies how salary scales post transaction (payslip) to gl accounts

add these codes in to hooks.php to create application links

$this->add_rapp_function(2, 'Payroll rules', $path_to_root.'/modules/payroll/manage/payrule_structure.php', 'SA_PRSETUP', MENU_MAINTENANCE);
        $this->add_rapp_function(2, 'Salary structure', $path_to_root.'/modules/payroll/manage/salary_structure.php', 'SA_PRSETUP', MENU_MAINTENANCE);

and add this payrule_structure.php to payroll/manage/

<?php

$page_security = 'SA_PRSETUP';
$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();
    
page(_($help_context = "Manage payroll accounts"), '', false, "", $js); 

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");
$selected_id = get_post('salary_scale_id','');

//---------------------------------------------------------------------------------------

function handle_submit(&$selected_id) {
    global $path_to_root, $Ajax;
        
    if ($selected_id) {    

        $payrule = array();
        foreach($_POST as $p =>$val) {
            
            if (substr($p,0,7) == 'Payroll' && $val == 1) {
                $a = substr($p,7);
                $payrule[] = (int)$a;
            }
        }
        
        if(exist_payroll($selected_id))
            update_payroll($selected_id,$payrule);    
        else
            add_payroll($selected_id,$payrule);
        
        $Ajax->activate('salary_scale_id');
        display_notification(_("Accounts have been updated."));
    } 
    else {

        display_warning(_("Select accounts first."));        
        set_focus('salary_scale_id');
    }

}

//-----------------------------------------------------------------------------------------

if (isset($_POST['submit'])) {
    
    handle_submit($selected_id);
}

//------------------------------------------------------------------------------------------

if (isset($_POST['delete'])) {

    $cancel_delete = 0;

    if ($cancel_delete == 0) {
        
        reset_payroll($selected_id);

        display_notification(_("Selected job position has been deleted."));
        unset($_POST['salary_scale_id']);
        $selected_id = '';
        $Ajax->activate('_page_body');
    }
}

function payroll_rule_settings($selected_id) {
    global $SysPrefs, $path_to_root;
    
    $new = true;
    foreach($_POST as $p =>$val) {
        
        if (substr($p,0,7) == 'Payroll')
            $_POST[$p] = '';
    }
    
    if ($selected_id) {
        
        $payroll_structure = get_payroll_structure($selected_id);

        if($payroll_structure) {
            
            $new = false;
            
            foreach($payroll_structure['payroll_rule'] as $rule_code) {

                $_POST['Payroll'.$rule_code] = 1;
            }
        }

        $_POST['salary_scale_id'] = $selected_id;
    }
    
    
    start_table(TABLESTYLE2);
    $th=array(_("Select Payroll Rules"),'');
    table_header($th);
    
    $result_rules=get_payroll_rules();
    
    while($row_rule=db_fetch($result_rules)) {

        check_row($row_rule["account_code"].' - '.$row_rule["account_name"], 'Payroll'.$row_rule["account_code"],null);    
    }
    end_table(1);

//---------------------------------------------------------------------------------
    
    div_start('controls');
    
    if($new) {
        
        submit_center('submit', _("Save"), true,_("Save payroll structure"),'default');
    }
    else {
        submit_center_first('submit', _("Update"), _('Update payroll rules data'), @$_REQUEST['popup'] ? true : 'default');
        submit_return('select', $selected_id, _("Select this salary scale and return to document entry."));
        submit_center_last('delete', _("Delete"), _('Delete salary scale data if have been never used'), true);
    }
    div_end();
}

//------------------------------------------------------------------------------------
 
start_form();

if (db_has_salary_scale()) {
    
    start_table(TABLESTYLE_NOBORDER);
    start_row();
    
    salary_scale_list_cells(_("Select salary scale: "), 'salary_scale_id', null, _('Select'), true, check_value('show_inactive'));
    check_cells(_("Show inactive:"), 'show_inactive', null, true);
    
    end_row();
    end_table(1);    
    if (get_post('_show_inactive_update')) {
        $Ajax->activate('salary_scale_id');
        set_focus('salary_scale_id');
    }
} 
else {
    hidden('salary_scale_id');
}

payroll_rule_settings($selected_id); 

hidden('popup', @$_REQUEST['popup']);
end_form();
end_page();

?>

add this payrule_structure_db.inc to payroll/includes/db and include it to payroll_db.inc

<?php

function exist_payroll_account($selected_account) {
    $sql = "SELECT account_code FROM ".TB_PREF."payroll_accounts WHERE account_code=".db_escape($selected_account);
    
    $result = db_query($sql,"Cannot retreive account");
    return (db_num_rows($result) > 0);
}

//----------------------------------------------------------------------------------

function get_payroll_accounts() {
    $sql = "SELECT ac.account_code, ac.account_name FROM ".TB_PREF."chart_master ac, ".TB_PREF."payroll_accounts pa WHERE ac.account_code = pa.account_code";
    
    return db_query($sql,'could not get accounts');
}

//----------------------------------------------------------------------------------

function add_payroll_account($pay_account) {
    $sql= "INSERT INTO ".TB_PREF."payroll_accounts (account_code) VALUES (".db_escape($pay_account).")";
    
    db_query($sql,"could not insert account");
}

//----------------------------------------------------------------------------------

function get_payroll_rules() {

    $sql = "SELECT ca.account_code, ca.account_name, pa.account_code FROM ".TB_PREF."chart_master ca, ".TB_PREF."payroll_accounts pa WHERE ca.account_code = pa.account_code ORDER BY ca.account_code";

    return db_query($sql,"could not get accounts");        
}

//----------------------------------------------------------------------------------

function add_payroll($salary_scale_id,$payroll_rule) {

    $sql = "INSERT INTO ".TB_PREF."payroll_structure (salary_scale_id, payroll_rule ) VALUES (".db_escape($salary_scale_id).",".db_escape(implode(';',$payroll_rule)).")";
    db_query($sql,"The payroll could not be added");    
}

//----------------------------------------------------------------------------------

function update_payroll($salary_scale_id,$payroll_rule) {

    $sql = "UPDATE ".TB_PREF."payroll_structure SET payroll_rule=".db_escape(implode(';', $payroll_rule))."WHERE salary_scale_id=".db_escape($salary_scale_id);
        
    db_query($sql,"The payroll could not be updated");

}

//---------------------------------------------------------------------------------

function exist_payroll($salary_scale_id) {

    $sql = "SELECT payroll_rule FROM ".TB_PREF."payroll_structure WHERE salary_scale_id=".db_escape($salary_scale_id);

    $result = db_query($sql, "Cannot retreive a salary structure");

    return (db_num_rows($result) > 0);
}

//---------------------------------------------------------------------------------

function reset_payroll($salary_scale_id) {

    $sql = "DELETE FROM ".TB_PREF."payroll_structure WHERE salary_scale_id =".db_escape($salary_scale_id);
        db_query($sql, "could not reset salary structure role");
}



//---------------------------------------------------------------------------------

function delete_payroll($job_id) {
    
    $sql = "DELETE FROM ".TB_PREF."payroll_structure WHERE payroll_rule=".db_escape($job_id);;
    db_query($sql,"cannot delete scale");
    commit_transaction();
}

//----------------------------------------------------------------------------------

function get_all_payroll($active=true) { //get only active jobnames by default

  if ($active == true)
    $act_sql = "WHERE inactive = 0";

  $sql = "SELECT payroll_rule, job_name FROM ".TB_PREF."payroll_structure $act_sql";
  display_notification($sql);
  return db_query($sql, "The jobnames table is inaccessible");

}

//-----------------------------------------------------------------------------------

function get_payroll_structure($salary_scale_id) {

    $sql = "SELECT * FROM ".TB_PREF."payroll_structure WHERE salary_scale_id=".db_escape($salary_scale_id);
    $result = db_query($sql, "could not get salaryscales");
    $row = db_fetch($result);
    
    if ($row != false) {
    
        $row['payroll_rule'] = explode(';', $row['payroll_rule']);
    }

    return $row;
}

?>
Phuong

Re: Payroll extension for practice

manage/salary_structure.php

<?php

$page_security = 'SA_PRSETUP';
$path_to_root = "../../..";

include($path_to_root . "/includes/db_pager.inc");
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();
    
page(_($help_context = "Manage salary structure"), '', false, "", $js); 

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");
$selected_id = get_post('salary_scale_id','');

//---------------------------------------------------------------------------------

function can_process($selected_id) {
    
    if (!$selected_id) {
        
        display_error(_("Select salary scale"));
        set_focus('salary_scale_id');
        return false;
    } 

    foreach($_POST as $p=>$val) {

        if(substr($p,0,7) == "Account") {

            if(input_num("Debit".$val) > 0 && input_num("Credit".$val)) {
                display_error(_("Both Credit and Debit amount for a payroll rule not allowed."));
                set_focus("Debit".$val);
                return false;
            }
        }
    }
    return true;
}

//----------------------------------------------------------------------------------

function handle_submit(&$selected_id) {
    global $path_to_root, $Ajax;

    if (!can_process($selected_id))
        return;

    $payroll_rules = array();
    foreach($_POST as $p=>$val) {
        if(substr($p,0,7) == "Account") {

            if(input_num("Debit".$val) > 0) {
                $type = DEBIT;
                $amount = @input_num("Debit".$val);
            }
            else {
                $type = CREDIT;
                $amount = @input_num("Credit".$val);
            }

            if($amount > 0) {
                $payroll_rules[] = array(
                    'salary_scale_id'=> $selected_id,
                    'pay_rule_id'     => $val,
                    'pay_amount'     => $amount,
                    'type'             => $type
                );
            }
        }
    }

    if(empty($payroll_rules)) {
        display_error(_("No data entered"));
    }
    else {
    
        if(exists_salary_structure($selected_id)) {
            reset_salary_structure($selected_id);
        }
        
        add_salary_structure($payroll_rules);
            
        display_notification(_("Salary structure updated."));        
    }
    $Ajax->activate('_page_body');
}
//---------------------------------------------------------------------------------

if (isset($_POST['submit'])) {
    handle_submit($selected_id);
}

//---------------------------------------------------------------------------------

if (isset($_POST['delete'])) {

    $cancel_delete = 0;

    if ($cancel_delete == 0) {    
        reset_salary_structure($selected_id);

        display_notification(_("Selected structure has been deleted."));
        unset($_POST['salary_scale_id']);
        $selected_id = '';
        $Ajax->activate('_page_body');
    }
}

function payroll_rules_settings($selected_id) {
    global $SysPrefs, $path_to_root;

    $new = true;

    $rules = array();

    $payroll_structure = get_payroll_structure($selected_id);
    
    if($payroll_structure) {

        foreach($payroll_structure['payroll_rule'] as $rule_code) {
            $ac = get_gl_account($rule_code);
            $rules[] = array(
                'account_input' => "Account".$rule_code,
                'debit_input'     => "Debit".$rule_code,
                'credit_input'    => "Credit".$rule_code,
                'account_code'    => $rule_code,
                'account_name'    => $ac['account_name'],
            );
            $_POST["Debit".$rule_code] = price_format(0);
            $_POST["Credit".$rule_code] = price_format(0);

        }
        $rsStr = get_salary_structure($selected_id);
        
        if(db_num_rows($rsStr) > 0) {
            $new = false;
            while($rowStr = db_fetch($rsStr)) {
                
                if($rowStr['type'] == DEBIT)
                    $_POST["Debit".$rowStr['pay_rule_id']] = $rowStr['pay_amount'];
                else 
                    $_POST["Credit".$rowStr['pay_rule_id']] = $rowStr['pay_amount'];
            }
        }

        br();
        start_table(TABLESTYLE2);
        $th = array(_("Payroll Rules"),_("Debit"),_("Credit"));
        table_header($th);
        
        foreach($rules as $rule) {            
            start_row();
                hidden($rule['account_input'],$rule['account_code']);
                label_cell($rule["account_name"]);
                amount_cells(null,$rule['debit_input']);
                amount_cells(null,$rule['credit_input']);
            end_row();
        }
        end_table(1);

        div_start('controls');
        
        if($new) {
            submit_center('submit', _("Save salary structure"), true, '', 'default');
        }
        else {
            submit_center_first('submit', _("Update"), _('Update salary structure data'), @$_REQUEST['popup'] ? true : 'default');
            submit_center_last('delete', _("Delete"), _('Delete salary structure data if have been never used'), true);
        }

        div_end();
    }
    else {
        display_error("Payroll structure not defined for this salary scale");
    }
}

//---------------------------------------------------------------------------------
 
start_form();

if (db_has_salary_scale()) {
    start_table(TABLESTYLE2);
    start_row();
    
    salary_scale_list_cells(_("Select a salary scale: "), 'salary_scale_id', null, _('Select salary scale'), true, check_value('show_inactive'));
    
    end_row();
    end_table();
    
    if (get_post('_show_inactive_update')) {
        $Ajax->activate('salary_scale_id');
        set_focus('salary_scale_id');
    }
} 
else {
    hidden('salary_scale_id');
}

if($selected_id)
    payroll_rules_settings($selected_id); 

hidden('popup', @$_REQUEST['popup']);
end_form();
end_page();

?>

salary_structure_db.inc

<?php

function add_salary_structure($pay_rules=array()) {

    if(empty($pay_rules))
        return false;

    begin_transaction();
    
        foreach($pay_rules as $rule) {
        
            insert_structure($rule['salary_scale_id'],
            $rule['pay_rule_id'],$rule['pay_amount'],$rule['type']);
        }
    commit_transaction();
}

//----------------------------------------------------------------------------

function insert_structure($salary_scale_id,$pay_rule_id,$pay_amount,$type) {

    $date_ = date2sql(Today());
    $sql = "INSERT INTO ".TB_PREF."salary_structure(date,salary_scale_id, pay_rule_id,pay_amount,type)VALUES ('"
    .$date_."',".db_escape($salary_scale_id).",".db_escape($pay_rule_id).","
    .db_escape($pay_amount).",".db_escape($type).")";

    db_query($sql, "could not add salary structure");
}

//----------------------------------------------------------------------------

function exists_salary_structure($salary_scale_id) {

    $sql = "SELECT id FROM ".TB_PREF."salary_structure WHERE salary_scale_id=".db_escape($salary_scale_id);

    $result = db_query($sql, "Cannot retreive a salary structure");

    return (db_num_rows($result) > 0);
}

//----------------------------------------------------------------------------

function reset_salary_structure($salary_scale_id) {

    $sql = "DELETE FROM ".TB_PREF."salary_structure WHERE salary_scale_id =".db_escape($salary_scale_id);
    db_query($sql, "could not reset salary structure role");
}

//----------------------------------------------------------------------------

function get_salary_structure($salary_scale_id) {

    $sql = "SELECT st.*,ac.account_name FROM ".TB_PREF."salary_structure st, ".TB_PREF."chart_master ac WHERE ac.account_code = st.pay_rule_id AND st.salary_scale_id =".db_escape($salary_scale_id);

      return db_query($sql, "Could not get salary structure");
}

//----------------------------------------------------------------------------

function get_emp_salary_structure($emp_id) {

    $sql = "SELECT str.* FROM ".TB_PREF."salary_structure str, ".TB_PREF."employees  emp WHERE str.salary_scale_id = emp.salary_scale_id AND emp.emp_id =".db_escape($emp_id);

    return db_query($sql, "Could not get employee salary structure");
}

?>
Phuong

Re: Payroll extension for practice

update.sql after edited for Payroll rules and Salary structure

-- 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_payroll_structure`

CREATE TABLE IF NOT EXISTS `0_payroll_structure` (
  `salary_scale_id` int(11) NOT NULL,
  `payroll_rule` text NOT NULL,
  KEY `salary_scale_id` (`salary_scale_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);
-- -----------------------------------------------------------------------------

-- Table structure for table `0_salary_structure`

CREATE TABLE IF NOT EXISTS `0_salary_structure` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `date` date NOT NULL,
  `salary_scale_id` int(11) NOT NULL,
  `pay_rule_id` int(11) NOT NULL,
  `pay_amount` double NOT NULL,
  `type` tinyint(4) NOT NULL COMMENT '0 for credit,1 for debit',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;

-- ------------------------------------------------------------------------------

ALTER TABLE `0_gl_trans` ADD `to_the_order_of` VARCHAR(225) NULL , ADD `payslip_no` INT(11) NOT NULL DEFAULT '0';
ALTER TABLE `0_gl_trans` ADD `from_date` DATE NOT NULL , ADD `to_date` DATE NOT NULL ;

-- ------------------------------------------------------------------------------

-- Table structure for table `0_paylsip_details`

CREATE TABLE IF NOT EXISTS `0_payslip_details` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `payslip_no` int(11) NOT NULL,
  `generated_date` date NOT NULL,
  `to_the_order_of` varchar(255) NOT NULL,
  `from_date` date NOT NULL,
  `to_date` date NOT NULL,
  `leaves` int(11) NOT NULL,
  `deductable_leaves` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;

-- ------------------------------------------------------------------------------

-- Table structure for table `0_payroll_accounts`

CREATE TABLE IF NOT EXISTS `0_payroll_accounts` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `account_code` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;

-- ------------------------------------------------------------------------------

Phuong

18 (edited by apmuthu 01/04/2017 04:13:23 am)

Re: Payroll extension for practice

There is no reference to the Employee ID in the "payslip_details" table yet which may be in the "payslips" table which has not been defined yet.

Post's attachments

FA_Payroll_Schema_draft_1.png 23.6 kb, 1 downloads since 2017-01-03 

You don't have the permssions to download the attachments of this post.

Re: Payroll extension for practice

apmuthu wrote:

There is no reference to the Employee ID in the "payslip_details" table yet which may be in the "payslips" table which has not been defined yet.

Thanks for point it out apmuthu. I'm planning to use "payslip_details" table for the report which has been not defined and i have no idea so we may ignore it at the moment. I might need more feedback from now on because FA becomes not familiar to me since I come in gl transaction functions and the reports. Hope to recieve more advices smile

Phuong

Re: Payroll extension for practice

Make a mockup of the final payslip printout pdf file and link it in here. We can then hammer out the tables needed. Highlight the data elements in different legend-ed colours for each of the following, data as pertinent to:
1. This pay slip instance only
2. All Pay Slips
3. This employee only
4. All such designations/pay scales
5. All such payment accounts

Only after the ERD as above is done, should we begin to make the module code and menu items.

21 (edited by notrinos 01/05/2017 01:58:34 am)

Re: Payroll extension for practice

apmuthu wrote:

Make a mockup of the final payslip printout pdf file and link it in here. We can then hammer out the tables needed. Highlight the data elements in different legend-ed colours for each of the following, data as pertinent to:
1. This pay slip instance only
2. All Pay Slips
3. This employee only
4. All such designations/pay scales
5. All such payment accounts

Only after the ERD as above is done, should we begin to make the module code and menu items.

I should have planned these before starting code, since this extension just an inspiration, I started jumping to codes without figure out  both what's going to happen behind the scenes and app is going to look. Ok, i'll try to complete what all I can by this way and maybe start again for another payroll ext soon.

Phuong

22 (edited by notrinos 01/05/2017 03:28:11 am)

Re: Payroll extension for practice

payroll_accounts.php create in payroll/manage for manage payroll gl accounts

<?php

$page_security = 'SA_PRSETUP';
$path_to_root = "../../..";

include_once($path_to_root . "/includes/session.inc");
add_access_extensions();
    
page(_($help_context = "Manage payroll accounts")); 

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");

simple_page_mode(false);
//----------------------------------------------------------------------------------

if ($Mode=='ADD_ITEM') {

    $input_error = 0;

    if (exist_payroll_account($_POST['AccountList'])) {
        $input_error = 1;
        display_error("Selected account has been used");
        set_focus('AccountList');
    }

    if (strlen($_POST['AccountList']) == 0) {
        $input_error = 1;
        display_warning(_("Select account first."));
        set_focus('AccountList');
    }

    if ($input_error !=1) {
        add_payroll_account($_POST['AccountList']);
        display_notification(_("Account has been added."));
        $Mode = 'RESET';
    }
}

//----------------------------------------------------------------------------------

if ($Mode == 'Delete') {

    if (payroll_account_used($selected_id)) {
        display_error(_("Cannot delete this account because payroll rules have been created using it."));
    }
    else {
        delete_payroll_account($selected_id);
        display_notification(_('Selected account has been deleted'));
    }
    $Mode = 'RESET';
}

if ($Mode == 'RESET') {
    $selected_id = '';
    $sav = get_post('show_inactive');
    unset($_POST);
    $_POST['show_inactive'] = $sav;
}

//----------------------------------------------------------------------------------

$result = get_payroll_accounts();

start_form();
start_table(TABLESTYLE2, "width='35%'");
$th = array(_('Code'), _('Description'), "");

table_header($th);

$k = 0; 
while ($myrow = db_fetch($result)) {

    alt_table_row_color($k);

    label_cell($myrow["account_code"]);
    label_cell($myrow["account_name"]);
    $id = htmlentities($myrow["id"]);
     delete_button_cell("Delete".$id, _("Delete"));
    
    end_row();
}

end_table(1);

//----------------------------------------------------------------------------------

start_table(TABLESTYLE_NOBORDER);

gl_all_accounts_list_cells(null, 'AccountList', null, false, false,
        _('Select account'), true, check_value('show_inactive'));
    check_cells(_("Show inactive:"), 'show_inactive', null, true);

end_table(1);

submit_add_or_update_center($selected_id == '', '', 'both');

end_form();

end_page();

and add these two function to payrule_structures_db.inc


function payroll_account_used($selected_id) {

    $sql= "SELECT pa.id, ps.payroll_rule, pa.account_code FROM ".TB_PREF."payroll_structure ps, ".TB_PREF."payroll_accounts pa WHERE pa.id=".$selected_id." AND ps.payroll_rule LIKE CONCAT('%',pa.account_code,'%')";
   
    $result = db_query($sql,"cannot get account");
   
    return (db_num_rows($result) > 0);
}

//----------------------------------------------------------------------------------

function delete_payroll_account($selected_id) {

    $sql = "DELETE FROM ".TB_PREF."payroll_accounts WHERE id=".$selected_id;
    $result = db_query($sql,'cannot delete account');
}

Phuong

23 (edited by notrinos 01/07/2017 02:58:19 am)

Re: Payroll extension for practice

add overtime table for update.sql


CREATE TABLE IF NOT EXISTS `0_overtime` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `overtime_rate` int(11) NOT NULL,
    `description` varchar(255) NOT NULL,
    `inactive` tinyint(4) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=1 ;

-- Dumbing data into  table `0_overtime`
INSERT INTO `0_overtime` VALUES
(1,1.5,"Regular overtime"),
(2,2,"Double time"),
(3,3,"Triple time");

insert these lines to hooks.php


$this->add_lapp_function(2, 'Overtime managment', $path_to_root.'/modules/payroll/manage/overtime.php', 'SA_PRSETUP', MENU_MAINTENANCE);

overtime_db.inc

<?php

function get_all_overtime($all=false) {
    $sql = "SELECT * FROM ".TB_PREF."overtime";
    if (!$all) $sql .= " WHERE !inactive";
    $sql .= " ORDER BY description";
    return db_query($sql,"cannot get overtime");
}

//--------------------------------------------------------------------

function get_overtime($unit)
{
    $sql="SELECT * FROM ".TB_PREF."overtime WHERE id=".db_escape($unit);

    $result = db_query($sql,"an overtime could not be retrieved");

    return db_fetch($result);
}

//--------------------------------------------------------------------

function write_overtime($selected, $rate, $description)
{
    if($selected!='')
        $sql = "UPDATE ".TB_PREF."overtime SET
         overtime_rate = ".db_escape($rate).",
         description = ".db_escape($description)."
            WHERE id = ".db_escape($selected);
    else
        $sql = "INSERT INTO ".TB_PREF."overtime
            (overtime_rate, description) VALUES( ".db_escape($rate).",
              ".db_escape($description).")";

    db_query($sql,"an overtime item could not be updated");
}

//--------------------------------------------------------------------

function delete_overtime($id)
{
    $sql="DELETE FROM ".TB_PREF."overtime WHERE id=".db_escape($id);

    db_query($sql,"an overtime item could not be deleted");
}
?>

overtime_ui.inc

<?php

function overtime_list($name, $selected_id=null, $spec_option=false, $submit_on_change=false, 
    $show_inactive=false, $editkey = false)
{
    global $all_items;

    $sql = "SELECT id, description FROM ".TB_PREF."overtime ";

    $mode = get_company_pref('no_leave_list');

    if ($editkey)
        set_editor('overtime', $name, $editkey);

    $ret = combo_input($name, $selected_id, $sql, 'id', 'description',
    array(
        'order' => array('description'),
        'search_box' => $mode!=0,
        'type' => 1,
        'size' => 20,
        'search' => array("description"),        
        'spec_option' => $spec_option === true ? _("All rates") : $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 overtime details') :
        _('Select overtime rate'),
        'show_inactive' => $show_inactive
    ) );
    if ($editkey)
        $ret .= add_edit_combo('overtime');
    return $ret;
}

//----------------------------------------------------------------------------------------------

function overtime_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 overtime_list($name, $selected_id, $all_option, $submit_on_change,
        $show_inactive, $editkey);
    echo "</td>\n";
}

//----------------------------------------------------------------------------------------------

function overtime_list_row($label, $name, $selected_id=null, $all_option=false, 
    $submit_on_change=false, $show_inactive=false, $editkey = false)
{
    echo "<tr>";
    overtime_list_cells($label, $name, $selected_id, $all_option, 
    $submit_on_change, $show_inactive, $editkey);
    echo "</tr>";
}

//----------------------------------------------------------------------------------------------

overtime.php

<?php

$page_security = 'SA_PRSETUP';
$path_to_root = "../../..";

include_once($path_to_root . "/includes/session.inc");
add_access_extensions();
    
page(_($help_context = "Manage overtime rates")); 

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");

simple_page_mode(false);
//----------------------------------------------------------------------------------

if ($Mode=='ADD_ITEM' || $Mode=='UPDATE_ITEM') {

    //initialise no input errors assumed initially before we test
    $input_error = 0;

    if (strlen($_POST['rate']) == 0) {
        $input_error = 1;
        display_error(_("The Overtime rate cannot be empty."));
        set_focus('rate');
    }
    if (strlen($_POST['description']) == 0) {
        $input_error = 1;
        display_error(_("The overtime description cannot be empty."));
        set_focus('description');
    }
    if (!is_numeric($_POST['rate'])) {
        $input_error = 1;
        display_error(_("Overtime rate must be a number."));
        set_focus('rate');
    }

    if ($input_error !=1) {
        write_overtime($selected_id, $_POST['rate'], $_POST['description'] );
        if($selected_id != '')
            display_notification(_('Selected overtime item has been updated'));
        else
            display_notification(_('New overtime item has been added'));
        $Mode = 'RESET';
    }
}

//----------------------------------------------------------------------------------

if ($Mode == 'Delete') {

    if (item_unit_used($selected_id)) {
        
        display_error(_("Cannot delete this overtime because items have been created using it."));
    }
    else {
        
        delete_overtime($selected_id);
        display_notification(_('Selected unit has been deleted'));
    }
    $Mode = 'RESET';
}

if ($Mode == 'RESET') {
    
    $selected_id = '';
    $sav = get_post('show_inactive');
    unset($_POST);
    $_POST['show_inactive'] = $sav;
}

//----------------------------------------------------------------------------------

$result = get_all_overtime(check_value('show_inactive'));

start_form();
start_table(TABLESTYLE, "width='40%'");
$th = array(_('Description'), _('Rates'), "", "");
inactive_control_column($th);

table_header($th);
$k = 0;

while ($myrow = db_fetch($result)) {

    alt_table_row_color($k);

    label_cell($myrow["description"]);
    label_cell($myrow["overtime_rate"]);
    $id = htmlentities($myrow["id"]);
    inactive_control_cell($id, $myrow["inactive"], 'overtime', 'id');
     edit_button_cell("Edit".$id, _("Edit"));
     delete_button_cell("Delete".$id, _("Delete"));
    
    end_row();
}

inactive_control_row($th);
end_table(1);

//----------------------------------------------------------------------------------

start_table(TABLESTYLE2);

if ($selected_id != '') {
     if ($Mode == 'Edit') {

        $myrow = get_overtime($selected_id);

        $_POST['rate'] = $myrow["overtime_rate"];
        $_POST['description']  = $myrow["description"];
    }
    hidden('selected_id', $myrow["id"]);
}
if ($selected_id != '' && item_unit_used($selected_id)) {
    label_row(_("Overtime rate:"), $_POST['rate']);
    hidden('rate', $_POST['rate']);
} else
    text_row(_("Overtime rate:"), 'rate', null, 20, 20);

text_row(_("Descriptive Name:"), 'description', null, 40, 50);

end_table(1);

submit_add_or_update_center($selected_id == '', '', 'both');

end_form();
end_page();

remember to include db and ui files into payroll_db.inc and payroll_ui.inc

Phuong

24 (edited by apmuthu 01/06/2017 08:50:50 am)

Re: Payroll extension for practice

Please alter all flag fields:

`inactive` TINYINT(4) NOT NULL DEFAULT '0',

to be:

`inactive` TINYINT(1) NOT NULL DEFAULT '0',

The following should achieve it:

ALTER TABLE `0_departments` CHANGE `inactive` `inactive` TINYINT(1) DEFAULT 0 NOT NULL;
ALTER TABLE `0_overtime`    CHANGE `inactive` `inactive` TINYINT(1) DEFAULT 0 NOT NULL;
ALTER TABLE `0_salaryscales` CHANGE `inactive` `inactive` TINYINT(1) DEFAULT 0 NOT NULL; 
ALTER TABLE `0_salary_structure` CHANGE `type` `type` TINYINT(1) NOT NULL COMMENT '0 for credit,1 for debit';

Attached is the sql after above changes.

Post's attachments

fa_payroll_2017-01-06.sql 3 kb, 9 downloads since 2017-01-06 

You don't have the permssions to download the attachments of this post.

Re: Payroll extension for practice

I have created payslip and payslip advice features now this extension can be useable
DOWNLOAD ZIP

Phuong