Location: PHPKode > projects > PhpProjectMaster > www/php/timesheet.php
<?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();
}


?>
Return current item: PhpProjectMaster