prefix}timesheet t
join {$wpdb->prefix}timesheet_approvers_approvies aa ON aa.approvie_user_id = t.user_id
join {$wpdb->prefix}timesheet_approvers ta on aa.approver_user_id = ta.user_id
and (ta.user_id = {$user_id} OR (ta.backup_user_id = {$user_id} and ta.backup_expires_on > now()))
join {$wpdb->prefix}timesheet_client_projects tcp on t.ProjectId = tcp.ProjectId
where approved = 0 and week_complete = 1";
$count = $db->get_row($sql);
return $count;
}
function employee_approver_check($includeBackups=true) {
global $wpdb;
$user_id = get_current_user_id();
$db = new time_sheets_db();
if ($includeBackups=='true') {
$sql = "select count(*) from {$wpdb->prefix}timesheet_approvers where (user_id = {$user_id}) OR (backup_user_id = {$user_id} and backup_expires_on > now())";
} else {
$sql = "select count(*) from {$wpdb->prefix}timesheet_approvers where user_id = {$user_id}";
}
$count = $db->get_var($sql);
return $count;
}
function do_timesheet_approval_processing() {
global $wpdb;
$db = new time_sheets_db();
$user_id = get_current_user_id();
check_admin_referer( 'timesheet_approval_processing');
$timesheets = $this->return_approval_list();
if ($timesheets) {
foreach ($timesheets as $timesheet) {
$objname = "sheet_{$timesheet->timesheet_id}";
$value = $_GET[$objname];
if ($value == "approve") {
$this->do_timesheet_approval($timesheet->timesheet_id);
}
if ($value == "reject") {
$this->do_timesheet_rejectapproval($timesheet->timesheet_id);
}
}
}
echo "
Timesheet(s) updated.
";
}
function email_on_approval($timesheet_id) {
$options = get_option('time_sheets');
$common = new time_sheets_common();
if (!$options['enable_email']) {
return;
}
global $wpdb;
$db = new time_sheets_db();
$sql = "select display_name
from {$wpdb->prefix}timesheet t
join {$wpdb->users} u on u.ID = t.user_id
where t.timesheet_id = %d";
$params = array($timesheet_id);
$user_login = $db->get_var($sql, $params);
$sql = "select user_email
from {$wpdb->users} u
join {$wpdb->prefix}timesheet_invoicers a on u.ID = a.user_id";
$users = $db->get_results($sql);
$subject = "There are time sheet(s) pending invoicing.";
$body = "A time sheet has been entered by {$user_login} which is approved and needs to be invoiced to the client..
It can be viewed and invoiced from the invoicing menu.";
foreach ($users as $user) {
$common->send_email ($user->user_email, $subject, $body);
}
}
function do_timesheet_split() {
global $wpdb;
$db = new time_sheets_db();
$sql = "SELECT GROUP_CONCAT(distinct COLUMN_NAME) columns
FROM `INFORMATION_SCHEMA`.`COLUMNS`
WHERE `TABLE_NAME`='{$wpdb->prefix}timesheet'
and COLUMN_NAME not in ('timesheet_id', 'monday_hours', 'tuesday_hours', 'wednesday_hours',
'thursday_hours', 'friday_hours', 'saturday_hours', 'sunday_hours', 'total_hours',
'monday_desc', 'tuesday_desc', 'wednesday_desc', 'thursday_desc',
'friday_desc', 'saturday_desc', 'sunday_desc', 'per_diem_days', 'hotel_charges',
'rental_car_charges', 'tolls', 'other_expenses', 'other_expenses_notes', 'mileage',
'flight_cost')";
$cols = $db->get_var($sql);
if ($_GET['new_start_date'] ==1) {
$cols = "{$cols}, tuesday_hours, wednesday_hours, thursday_hours, friday_hours, saturday_hours, sunday_hours, tuesday_desc, wednesday_desc, thursday_desc, friday_desc, saturday_desc, sunday_desc";
$update_cols = "tuesday_hours = 0, wednesday_hours=0, thursday_hours=0, friday_hours=0, saturday_hours=0, sunday_hours=0, tuesday_desc='', wednesday_desc='', thursday_desc='', friday_desc='', saturday_desc='', sunday_desc=''";
}
if ($_GET['new_start_date'] ==2) {
$cols = "{$cols}, wednesday_hours, thursday_hours, friday_hours, saturday_hours, sunday_hours, wednesday_desc, thursday_desc, friday_desc, saturday_desc, sunday_desc";
$update_cols = "wednesday_hours=0, thursday_hours=0, friday_hours=0, saturday_hours=0, sunday_hours=0, wednesday_desc='', thursday_desc='', friday_desc='', saturday_desc='', sunday_desc=''";
}
if ($_GET['new_start_date'] ==3) {
$cols = "{$cols}, thursday_hours, friday_hours, saturday_hours, sunday_hours, thursday_desc, friday_desc, saturday_desc, sunday_desc";
$update_cols = "thursday_hours=0, friday_hours=0, saturday_hours=0, sunday_hours=0, thursday_desc='', friday_desc='', saturday_desc='', sunday_desc=''";
}
if ($_GET['new_start_date'] ==4) {
$cols = "{$cols}, friday_hours, saturday_hours, sunday_hours, friday_desc, saturday_desc, sunday_desc";
$update_cols = "friday_hours=0, saturday_hours=0, sunday_hours=0, friday_desc='', saturday_desc='', sunday_desc=''";
}
if ($_GET['new_start_date'] ==5) {
$cols = "{$cols}, saturday_hours, sunday_hours, saturday_desc, sunday_desc";
$update_cols = "saturday_hours=0, sunday_hours=0, saturday_desc='', sunday_desc=''";
}
if ($_GET['new_start_date'] ==6) {
$cols = "{$cols}, sunday_hours, sunday_desc";
$update_cols = "sunday_hours=0, sunday_desc=''";
}
$sql = "INSERT INTO {$wpdb->prefix}timesheet
({$cols})
SELECT {$cols} FROM {$wpdb->prefix}timesheet WHERE timesheet_id = %d";
$parms = array(intval($_GET['timesheet_id']));
$db->query($sql, $parms);
$sql = "UPDATE {$wpdb->prefix}timesheet set {$update_cols} WHERE timesheet_id = %d";
$db->query($sql, $parms);
echo '
Timesheet has been split.
';
}
function split_timesheet(){
global $wpdb;
$db = new time_sheets_db();
$common = new time_sheets_common();
$sql = "select start_date from {$wpdb->prefix}timesheet where timesheet_id = %d";
$parms = array(intval($_GET['timesheet_id']));
$start_date = $db->get_var($sql, $parms);
echo "";
}
function approve_timesheet(){
$common = new time_sheets_common();
$entry = new time_sheets_entry();
$options = get_option('time_sheets');
if ($_GET['action']=='preapprove') {
$entry->show_timesheet();
} elseif ($_GET['action']=='split') {
$this->split_timesheet();
} else {
echo " ";
echo "
";
$this->show_approval_list();
echo "
";
$common->show_clients_on_retainer();
echo "
";
}
}
function do_timesheet_rejectapproval($timesheet_id) {
global $wpdb;
$db = new time_sheets_db();
$common = new time_sheets_common();
$sql = "update {$wpdb->prefix}timesheet
set marked_complete_by=NULL,
marked_complete_date = NULL,
week_complete=0
where timesheet_id=%d";
$params=array($timesheet_id);
$db->query($sql, $params);
$subject = "Your timesheet(s) need to be reviewed and updated";
$body = "Timesheet {$timesheet_id} needs to be reviewed and updated";
$sql = "select user_email
from {$wpdb->users} u
join {$wpdb->prefix}timesheet t ON u.ID = t.user_id
where t.timesheet_id = %d";
$email = $db->get_var($sql, $params);
$common->send_email ($email, $subject, $body);
}
function return_approval_list() {
global $wpdb;
$db = new time_sheets_db();
$user_id = get_current_user_id();
$options = get_option('time_sheets');
$sql = "select t.timesheet_id, u.user_login, t.start_date, c.ClientName client_name, cp.ProjectName project_name, EmbargoPendingProjectClose, cp.notes, u.display_name,
date_add(t.start_date, INTERVAL CASE WHEN monday_hours != 0 then 0 when tuesday_hours != 0 then 1 when wednesday_hours != 0 then 2 when thursday_hours != 0 then 3 when friday_hours != 0 then 4 when saturday_hours != 0 then 5 when sunday_hours != 0 then 6 else 0 end DAY) first_billed_day
from {$wpdb->prefix}timesheet t
JOIN {$wpdb->prefix}timesheet_clients c ON t.ClientId = c.ClientId
JOIN {$wpdb->prefix}timesheet_client_projects cp ON t.ProjectId = cp.ProjectId
join {$wpdb->users} u on t.user_id = u.ID
join {$wpdb->prefix}timesheet_approvers_approvies aa ON aa.approvie_user_id = u.ID
join {$wpdb->prefix}timesheet_approvers ta on aa.approver_user_id = ta.user_id
and (ta.user_id = {$user_id} OR (ta.backup_user_id = {$user_id} and ta.backup_expires_on > now()))
where t.week_complete = 1 and approved = 0 ";
if (!$_GET['show_embargoed']) {
$sql = "$sql and (EmbargoPendingProjectClose = 0 or EmbargoPendingProjectClose IS NULL)";
}
$IsRetainer = "-1";
if ($_GET['show_retainers']) {
$IsRetainer = $IsRetainer . ", 1";
}
if (!$_GET['hide_nonretainers']) {
$IsRetainer = $IsRetainer . ", 0";
}
$sql = $sql . " and cp.ProjectId in (select ProjectId from {$wpdb->prefix}timesheet_client_projects where IsRetainer in ({$IsRetainer})) order by t.start_date";
$timesheets = $db->get_results($sql);
return $timesheets;
}
function do_timesheet_approval($timesheet_id){
global $wpdb;
$db = new time_sheets_db();
$options = get_option('time_sheets');
$user_id = get_current_user_id();
$entry = new time_sheets_entry();
$invoice = new time_sheets_queue_invoice();
$sql = "select p.*
from {$wpdb->prefix}timesheet_client_projects p
join {$wpdb->prefix}timesheet t on t.ProjectId = p.ProjectId
where t.timesheet_id = %d";
$params = array($timesheet_id);
$project = $db->get_row($sql, $params);
$sql = "update {$wpdb->prefix}timesheet
set approved = 1,
approved_by = $user_id,
approved_date = CURDATE()
where timesheet_id = %d";
$params = array($timesheet_id);
$db->query($sql, $params);
if ($project->flat_rate==1) {
$invoice->do_timesheet_invoicing_one_timesheet($timesheet_id, 1, $user_id, 0);
}
if ($options['queue_order']=='parallel') {
$payrolled = $invoice->should_be_payrolled($timesheet_id);
$sql = "update {$wpdb->prefix}timesheet
set payrolled = {$payrolled}
where timesheet_id = %d";
$params = array($timesheet_id);
$db->query($sql, $params);
}
$this->email_on_approval($timesheet_id);
$entry->check_overages($timesheet_id);
}
function show_approval_list() {
global $wpdb;
$db = new time_sheets_db();
$user_id = get_current_user_id();
$common = new time_sheets_common();
$timesheets = $this->return_approval_list();
if ($_GET['show_embargoed']) {
$embargod_parm = "&show_embargoed=1";
}
if ($_GET['show_retainers']) {
$retainers_parm = "&show_retainers=checked";
}
if ($_GET['hide_nonretainers']) {
$retainers_parm = $retainers_parm . "&hide_nonretainers=checked";
}
if ($timesheets) {
echo "";
} else {
echo "No time sheets to approve.";
}
}
}