<?php
/*
timesheet management
(c) 2004-2007 by "Oleg Savchuk" <hide@address.com>
part of phpProjectMaster project
http://phpprojmaster.sourceforge.net
The contents of this file are subject to the GNU GENERAL PUBLIC LICENSE
http://www.gnu.org/copyleft/gpl.html
*/
session_start();
require_once "../inc/sitelib.php" ;
require_once "../inc/form_utils.php" ;
require_once "../inc/user.php" ;
require_once "../inc/project.lib.php" ;
require_once "../inc/task.lib.php" ;
require_once "../inc/workcode.lib.php" ;
require_once "../inc/timesheet.lib.php" ;
require_once "../inc/site_value.php" ;
check_access(100); //if you don't need this - you can also don't requite inc/user.php
global_init();
//********* variables
$tbl_name = $timesheet_vars['table_name'];
$tbl_key = $timesheet_vars['table_key_id'];
$item_id = $_REQUEST['id']+0;
$this_tpl_dir = '/admin/timesheet';
$tbl_fields = 'p_id t_id wc_id idesc worktime workdate status'; //fields to save
//********* action!
$CGI_ACTIONS=array(
'' => 'show_item_list',
'SaveList' => 'save_item_list',
'AddNew' => 'show_one_item',
'Edit' => 'show_one_item',
'SaveRec' => 'save_one_item',
'DelRec' => 'delete_item',
);
go_action();
exit;
//***************************
function show_item_list(){
global $timesheet_vars;
global $tbl_name, $tbl_key;
global $this_script, $this_tpl_dir, $root_url, $MAX_PAGE_ITEMS;
$moreurl=''; //additional url params for nav
$where=" status<>127 and u_id=".$_SESSION['u_id'];
$weekend=$_SESSION['timesheet_weekend'];
if (!$weekend) $weekend=date2weekend(time());
if ($_REQUEST['GoPrevWeek']){
$weekend=strtotime("-7 day",$weekend);
}elseif ($_REQUEST['GoNextWeek']){
$weekend=strtotime("+7 day",$weekend);
}
$_SESSION['timesheet_weekend']=$weekend;
$where.=" and TO_DAYS(workdate)>TO_DAYS(".dbq(Unix2SQLDate( strtotime("-7 day",$weekend) )).")
and TO_DAYS(workdate)<=TO_DAYS(".dbq(Unix2SQLDate($weekend)).")
";
//******** filter options
$page=get_page_option('page', $tbl_name."_lastpage");
$pagenum=get_page_option('pagenum', $tbl_name."_pagenum");
if (!$pagenum) $pagenum=$MAX_PAGE_ITEMS;
$page_offset=$page*$pagenum;
//********** sorting
$sortby=get_page_option('sortby', $tbl_name."_sortby", 'str');
if (!$sortby) $sortby='workdate';
$SORTSQL=array(
'id' => 'tt_id',
// 'iname' => 'iname',
'add_time' => 'add_time desc',
'workdate' => 'workdate, add_time',
);
$ordsql=$SORTSQL[$sortby];
//**********
$search_str=$_REQUEST['s'];
if ($search_str){
$where.=" and (idesc like ".dbq("%$search_str%")."
)";
$moreurl.=(($moreurl)?'&':'').urlencode($search_str);
}
//get list of items
$sql="select SQL_CALC_FOUND_ROWS *
from $tbl_name
where $where
order by $ordsql
LIMIT $page_offset, $pagenum
";
$rows=db_array($sql);
$total_items=db_value("SELECT FOUND_ROWS()");
foreach($rows as $k => $row){
$rows[$k]=array_merge($row,array(
'p_id_name' => get_project_name($row['p_id']),
't_id_name' => get_task_name($row['t_id']),
'wc_id_name' => get_workcode_name($row['wc_id']),
'worktime' => sec2time($row['worktime']),
'workdate_human' => SQLDate2Str($row['workdate'],1),
'add_time_human' => SQLDate2Str($row['add_time'],1),
));
}
$list_nav='';
if ($total_items>$pagenum){
// $total_pages=ceil($total_items/$pagenum);
$list_nav=make_List_Navigation($page, $total_items, $pagenum, "$this_script?".$moreurl."&page=", '', 'showall');
# $list_nav=make_List_Navigation2($page, $total_items, $pagenum, "$this_script?".$moreurl."&page=");
}
$ps=array(
'item_datarow' => $rows,
'item_datarow_ctr' => count($rows),
'list_navigation' => $list_nav,
//filters
'search_str' => $search_str,
'sortby' => $sortby,
'pagenum' => $pagenum,
'weekend_human' => SQLDate2Str(Unix2SQLDate($weekend),1),
);
$ps=array_merge($ps, get_userinfo());
parse_page($GLOBALS['this_tpl_dir']."/list", $GLOBALS['PAGE_TPL_ADMIN'], $ps);
}
//******* do action with checkboxed items
function save_item_list(){
global $tbl_name, $tbl_key;
$cbses=$_REQUEST['cb'];
foreach($cbses as $item_id => $value){
if (!$item_id) continue;
// $hITEM=get_quotes($item_id);
$IFORM=array(
'status' => 127,
);
$sql="update $tbl_name set ".get_sqlupdate_set($IFORM).", upd_time=now(), upd_u_id=".$_SESSION['u_id']." where $tbl_key=$item_id";
// rw($sql);
db_query($sql);
}
show_item_list();
}
//***************************
//if $forceform=1 - redisplay values from FORM, not DB
function show_one_item($forceform=0){
global $timesheet_vars;
global $item_id, $tbl_key;
$hITEMDB=array();
#!!!TODO check access - should only employees itself to access their tiemsheet items?
if ( !isset($_SESSION['timesheet_wd_tasks']) ) $_SESSION['timesheet_wd_tasks']=1;
$hITEM=$_REQUEST['item'];
if (!$hITEM){ //if not set - this is initial form call - we can make here initializations
$hITEM['p_id']=$_SESSION['timesheet_p_id']+0;
$hITEM['t_id']=$_SESSION['timesheet_t_id']+0;
$hITEM['wc_id']=$_SESSION['timesheet_wc_id']+0;
$hITEM['workdate']=SQLDate2Str(Unix2SQLDate( time() ));
$hITEM['worktime']="0:00";
$hITEM['wd_tasks']= $_SESSION['timesheet_wd_tasks'];
}
$item_id=$_REQUEST['id']+0;
$hITEM[$tbl_key]=$item_id;
if ($item_id){ #edit mode
$hITEMDB=get_timesheet($item_id);
$hITEMDB['worktime']=sec2time($hITEMDB['worktime']);
}
if ($forceform){
$hITEM=array_merge($hITEMDB,$hITEM); //if redisplay from the FORM - merge with DB record, so read-only fields will be displayed too
}else{
if ($item_id){//only if Edit mode
$hITEM=&$hITEMDB; //making $hITEM same as $hITEMDB (no array copy)
$hITEM['wd_tasks']= $_SESSION['timesheet_wd_tasks'];
$hITEM['workdate']=SQLDate2Str($hITEM['workdate']);
}
}
$t_sql='';
$wd=StrDate2SQL($hITEM['workdate']);
if ($hITEM['wd_tasks']==1 ){
$t_sql=" and ( TO_DAYS(add_time)=TO_DAYS(".dbq( $wd ).") or TO_DAYS(upd_time)=TO_DAYS(".dbq( $wd )."))";
}
//display Workdate short timesheet
$wdt_rows=array();
$worktime_total=0;
//get list of items
$sql="select SQL_CALC_FOUND_ROWS *
from timesheet
where status<>127 and u_id=".$_SESSION['u_id']." and TO_DAYS(workdate)=TO_DAYS(".dbq( $wd ).")
order by add_time
";
$wdt_rows=db_array($sql);
foreach($wdt_rows as $k => $row){
$worktime_total+=$row['worktime'];
$wdt_rows[$k]=array_merge($row,array(
'p_id_name' => get_project_name($row['p_id']),
't_id_name' => get_task_name($row['t_id']),
'wc_id_name' => get_workcode_name($row['wc_id']),
'worktime' => sec2time($row['worktime']),
// 'add_time_human' => SQLDate2Str($row['add_time'],1),
));
}
$worktime_left=get_site_value('site.max_daily_workhours')*3600-$worktime_total;
$ps=array(
'select_p_id' => get_combo_select_sql("select p_id, LEFT(iname,64) from project where status<>127 order by iname", $hITEM['p_id']),
'select_t_id' => get_combo_tree_sql("select t_id, parent_t_id, LEFT(iname,64) from task where status<>127 $t_sql and p_id=".dbq($hITEM['p_id'])." order by iname", $hITEM['t_id']),
'select_wc_id' => get_combo_select_sql("select wc_id, LEFT(iname,64) from workcode where status<>127 order by iname", $hITEM['wc_id']),
'workdate_input' => date2htmlInput_jq("item[workdate]", $hITEM['workdate']),
'workdate_human' => $hITEM['workdate'],
'add_u_id_name' => ($hITEM['add_u_id'])?get_user_name($hITEM['add_u_id']):'',
'upd_u_id_name' => ($hITEM['upd_u_id'])?get_user_name($hITEM['upd_u_id']):'',
'wdt_datarow' => $wdt_rows,
'wdt_datarow_ctr' => count($wdt_rows),
'worktime_total' => sec2time($worktime_total),
'worktime_left' => sec2time($worktime_left),
);
$ps=array_merge($ps, $hITEM);
$ps=array_merge($ps, get_userinfo());
parse_page($GLOBALS['this_tpl_dir']."/edit", $GLOBALS['PAGE_TPL_ADMIN'], $ps);
}
//*************************** save item info
function save_one_item(){
global $timesheet_vars;
global $tbl_name, $tbl_key, $tbl_fields;
$item_id=$_REQUEST['id']+0;
$_SESSION['timesheet_wd_tasks']=$_REQUEST['item']['wd_tasks']+0;
if (validate_item($item_id, $_REQUEST['item'])){
$hITEM=get_timesheet($item_id);
//read from form only necessary/allowed fields
$IFORM=form2dbhash($_REQUEST['item'], "$tbl_fields");
//make some adjustments
//convert date from human str to SQL format
$IFORM['workdate']=StrDate2SQL($IFORM['workdate']);
$IFORM['worktime']=time2sec($IFORM['worktime']); //convert
//remember last selection
$_SESSION['timesheet_p_id']=$IFORM['p_id']+0;
$_SESSION['timesheet_t_id']=$IFORM['t_id']+0;
$_SESSION['timesheet_wc_id']=$IFORM['wc_id']+0;
if ($item_id){ //id exists - update record
$IFORM['upd_u_id']=$_SESSION['u_id'];
$sql="update $tbl_name set ".get_sqlupdate_set($IFORM).", upd_time=now() where $tbl_key=$item_id";
// logger($sql);
db_query($sql);
$GLOBALS['green_msg']=lng("Record has been modified");
} else { //id not exits - insert record
$IFORM['u_id']=$_SESSION['u_id'];
$IFORM['add_u_id']=$_SESSION['u_id'];
$sql="insert into $tbl_name ".get_sqlinsert_set($IFORM,', add_time',', now()');
// logger($sql);
$sth=db_query($sql);
$item_id=get_identity();
$GLOBALS['green_msg']=lng("New Record has been added");
}
$_REQUEST['id']=$item_id;
if ($_REQUEST['AndAddNext']){
unset($_REQUEST['item']);
unset($_REQUEST['id']);
}
show_one_item();
} else {
show_one_item(1);
}
}
//################# Validate item form values in IFORM
function validate_item($item_id=0, $IFORM){
global $timesheet_vars, $err_msg;
// print_r($IFORM);
$REQFLD=array(
'workdate' => lng('Work Date'),
'worktime' => lng('Work Time'),
'p_id' => lng('Project'),
't_id' => lng('Task'),
'wc_id' => lng('Work Code'),
'idesc' => lng('Description'),
);
// if (!$item_id) $REQFLD['xxx']=lng('xxx'); //add validation only for Edit mode
//VALIDATE REQUIRED FIELDS
if (!$err_msg) { $err_msg=validate_form($IFORM, $REQFLD); }
if (!$err_msg && time2sec($IFORM['worktime'])==0){
$err_msg=lng("Please enter non-zero Work Time.");
}
//VALIDATE if field unique
// - duplicate entries (p_id, t_id, wc_id and idesc - equal)
if (!$err_msg ){
$sql="select count(*) from timesheet
where status<>127
and u_id=".dbq($_SESSION['u_id'])."
and TO_DAYS(workdate)=TO_DAYS(".dbq( StrDate2SQL($IFORM['workdate']) ).")
and p_id=".dbq($IFORM['p_id'])."
and t_id=".dbq($IFORM['t_id'])."
and wc_id=".dbq($IFORM['wc_id'])."
and idesc=".dbq($IFORM['idesc'])."
and tt_id<>$item_id
";
if ( db_value($sql)>0 ){
$err_msg=lng("Record with such Date, Project, Task, Work Code, Description already exists. Please, change your record.");
}
}
// - check for physical overtime (>24)
if (!$err_msg ){
$sql="select sum(worktime)
from timesheet
where status<>127
and u_id=".dbq($_SESSION['u_id'])."
and TO_DAYS(workdate)=TO_DAYS(".dbq( StrDate2SQL($IFORM['workdate']) ).")
and tt_id<>$item_id
";
$sum=db_value($sql)+0+time2sec($IFORM['worktime']);
if ( $sum > $GLOBALS['MAX_DAILY_SECS'] ){
$err_msg=lng("You can't work more than 24h per day! Please, change your record.");
}
}
if ($err_msg) { return 0 ;}
return 1;
}
//************************
function delete_item(){
$item_id=$_REQUEST['id']+0;
delete_timesheet($item_id);
show_item_list();
}
?>