Reorganized few places in the code and added printable payslip.
301 06/17/2017 11:12:39 am
Re: Payroll extension for practice (45 replies, posted in Modules Add-on's)
302 06/13/2017 01:27:59 am
Re: Maybe Error? in Profit and Loss Statement (7 replies, posted in Banking and General Ledger)
@tomle Please check if Payroll Expenses Transactions exists or not in the database, I think if you did not make any trans it won't be show
303 06/05/2017 04:35:12 pm
Topic: 2.4.1 php7 issue. (1 replies, posted in Report Bugs here)
When I switched php to 7.1.1, FA 2.4.1 always shows an empty notification box on top of every pages.
304 06/05/2017 05:34:23 am
Topic: 2.4.1 dashboard issue. (1 replies, posted in Report Bugs here)
I'm not sure this is a bug, in the setup tab dashboard, the chart of Top 3 suppliers shows bar for Customer.
This happened when I create database from en_US-demo.sql
305 04/04/2017 01:59:25 pm
Re: Payroll extension for practice (45 replies, posted in Modules Add-on's)
Ajmal, try to rename folder to payroll instead of Payroll
306 03/26/2017 02:23:58 am
Re: Payroll extension for practice (45 replies, posted in Modules Add-on's)
Fixed issue of attendance sheet display. Replace line 61 of inquiry/attendance_sheet.php with:
$cols["<div style='background:#FFCCCC'>".$day->format('d')."</div><p hidden>".$day->format('m')."</p>"] = array('align'=>'center');
307 02/17/2017 12:44:16 pm
Re: Needed idea to work-arround diff. decimal places for unit price/amount (5 replies, posted in Setup)
Oh sorry! That was not good solution, instead of that you just add $line_total = round($line_total*100)/100; into line 170.
308 02/17/2017 11:54:56 am
Re: Needed idea to work-arround diff. decimal places for unit price/amount (5 replies, posted in Setup)
If you can edit code just replace lines 222 of sales/includes/ui/sales_order_ui.inc with $display_sub_total = round(price_format($total + input_num('freight_cost'))*100)/100; and line 227 with $display_total = round(price_format(($total + input_num('freight_cost') + $tax_total))*100)/100;
309 02/10/2017 07:00:43 am
Re: Payroll extension for practice (45 replies, posted in Modules Add-on's)
Added time attendance feature and mod payslip calculation based on timesheet DOWNLOAD
310 02/08/2017 07:22:08 am
Re: Create local module for FA 2.4RC (1 replies, posted in Modules Add-on's)
Change 'version' => '-' to 'version' => '2.4.0-2' in both installed_extensions.php and company/x/installed_extensions.php
Or you can commented lines 215-220 in admin/inst_modules.php then uncomment them after active your ext
311 02/04/2017 02:08:13 pm
Re: Payroll extension for practice (45 replies, posted in Modules Add-on's)
boxygen wrote:
By the way while installing with 2.4RC1 I am receiving following error.
Package 'Payroll' is incompatible with current application version and cannot be activated. Check Install/Activate page for newer package version.
With 2.3.25 it installed successfully but had problem in accessing pages.
This solved on post #4
312 02/02/2017 06:31:05 am
Re: Payroll extension for practice (45 replies, posted in Modules Add-on's)
did you installed on 2.3 @boxygen ? It works with 2.4 only.
313 01/16/2017 02:11:35 am
Re: Payroll extension for practice (45 replies, posted in Modules Add-on's)
I have found an issue with DateTime class when generating payslip and fixed it here.
Change these lines in the payslip_ui.inc from
$emp_id = $_POST['person_id'];
$from_date = $_POST['from_date'];
$to_date = $_POST['to_date'];
to
$emp_id = $_POST['person_id'];
$from_date = $_POST['from_date'];
$from_date = DateTime::createFromFormat("m-d-Y H:i:s", $from_date);
$to_date = $_POST['to_date'];
$to_date = DateTime::createFromFormat("m-d-Y H:i:s", $to_date);
314 01/13/2017 01:24:58 pm
Re: Payroll extension for practice (45 replies, posted in Modules Add-on's)
@apmuthu: thanks for your corrections.
maybe someday I'll create another payroll module for more features and hope to receive more advices from you.
315 01/13/2017 07:45:27 am
Re: Payroll extension for practice (45 replies, posted in Modules Add-on's)
I have created payslip and payslip advice features now this extension can be useable
DOWNLOAD ZIP
316 01/06/2017 08:19:25 am
Re: Payroll extension for practice (45 replies, posted in Modules Add-on's)
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
317 01/05/2017 01:53:46 am
Re: Payroll extension for practice (45 replies, posted in Modules Add-on's)
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');
}
318 01/05/2017 01:43:28 am
Re: Payroll extension for practice (45 replies, posted in Modules Add-on's)
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 accountsOnly 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.
319 01/04/2017 06:19:01 am
Re: Payroll extension for practice (45 replies, posted in Modules Add-on's)
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 
320 01/04/2017 02:28:18 am
Re: Payroll extension for practice (45 replies, posted in Modules Add-on's)
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 ;-- ------------------------------------------------------------------------------
321 01/03/2017 01:16:42 pm
Re: Payroll extension for practice (45 replies, posted in Modules Add-on's)
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");
}
?>322 01/03/2017 01:01:41 pm
Re: Payroll extension for practice (45 replies, posted in Modules Add-on's)
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;
}
?>323 01/02/2017 02:07:28 am
Re: Payroll extension for practice (45 replies, posted in Modules Add-on's)
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
324 01/01/2017 06:21:45 am
Re: Payroll extension for practice (45 replies, posted in Modules Add-on's)
Next post will show salary_scale functions, it similar to department structure.
325 01/01/2017 03:32:46 am
Re: Payroll extension for practice (45 replies, posted in Modules Add-on's)
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);
}
//---------------------------------------------------------------------------------------
?>