function UpdateUserProductivity( $where, $id, $contactid=0, $start_stop='', $statustype=0 )
{
global $UserID;
global $db;
global $RootPath;
//10.01.2012 ghh - added to include our activities file in case we need it
include_once( $RootPath."/includes/activities.php" );
$activitiesquery = '';
switch( $where )
{
case 1://ticket
if ( $start_stop == 'start' )//we are starting time
{
//TKS 11.16.2011 added projectID so we can easily limit results in report
$query = "select proTasks.UserID, proProject.ID as ProjectID from proTasks, proProject, proSection, proDetailHeader
where proTasks.ID = ".$id." and proTasks.PID = proDetailHeader.ID and proDetailHeader.PID = proSection.ID
and proSection.PID = proProject.ID";
if ( !$result = $db->sql_query( $query ) )
{
LogError( 8774, $query ."<br>".$db->sql_error() );
return false;
}
$row = $db->sql_fetchrow( $result );
if ( $row[ "UserID" ] == $UserID )
$is_assigned = 1;
else
$is_assigned = 0;
$query = "insert into genUserProductivity ( TaskID, UserID, StartTime, StatusType, isUserAssigned, ProjectID )
values ( ".$id.", ".$UserID.", UTC_TIMESTAMP, ".$statustype.", ".$is_assigned.", ".$row[ "ProjectID" ]." )";
if ( !$result = $db->sql_query( $query ) )
{
LogError( 8775, $query ."<br>".$db->sql_error() );
return false;
}
}
if ( $start_stop == 'stop' )//we are stoping time
{
$query = "update genUserProductivity set EndTime = UTC_TIMESTAMP, TotalTime = sec_to_time( Unix_timestamp( utc_timestamp ) - unix_timestamp( StartTime ) )
where TaskID = ".$id." and EndTime is NULL and UserID = ".$UserID;
if ( !$result = $db->sql_query( $query ) )
{
LogError( 8776, $query ."<br>".$db->sql_error() );
return false;
}
//10.01.2012 ghh - added to deal with adding this entry to our activities panel
//first we need to grab some info from the ticket itself
$query = "select Title, Description, TotalTime, StartTime, EndTime
from proTasks, genUserProductivity
where proTasks.ID=$id
and genUserProductivity.TaskID=proTasks.ID
order by EndTime desc limit 1";
if ( !$result = $db->sql_query( $query ) )
{
LogError( 10907, $query ."<br>".$db->sql_error() );
return false;
}
$activityrow = $db->sql_fetchrow( $result );
$activitiesquery = "insert into activities ( PrimaryID, TypeID, UserID, ActivityDate,
Title,
StartTime, EndTime, Duration ) values ( $id, 9, $UserID, now(),
'".addslashes( $activityrow[Title] )."','$activityrow[StartTime]',
'$activityrow[EndTime]','$activityrow[TotalTime]' )";
}
if ( $start_stop == 'complete' )//we are completing a ticket and need to stop time on all users
{
//TKS 05.22.2014 #52713 query to see if this person has time logging on this ticket when they hit complete/submit to testing
//and if so, set a flag to insert a record into the activities panel for the time
$query = "select * from genUserProductivity where UserID = ".$UserID." and TaskID = ".$id." and EndTime is NULL";
if ( !$result = $db->sql_query( $query ) )
{
LogError( 14699, $query ."<br>".$db->sql_error() );
return false;
}
if ( $db->sql_numrows( $result ) == 0 )
$insert_time = false;
else
$insert_time = true;
$query = "update genUserProductivity set EndTime = UTC_TIMESTAMP, TotalTime = sec_to_time( Unix_timestamp( utc_timestamp ) - unix_timestamp( StartTime ) )
where TaskID = ".$id." and EndTime is NULL";
if ( !$result = $db->sql_query( $query ) )
{
LogError( 8781, $query ."<br>".$db->sql_error() );
return false;
}
//TKS 08.29.2013 #40204 this query was grabbing the status ( which is updated before
//this function gets called ) and the last times logged. The problem with this is
//you may have logged these times yesterday but completing or sending to testing today
//which makes this inaccurate. SO I am going to change the start and end time to just insert
//for now and now + 1 second and set the total time to 1 sec so it is more accurate.
//as a result this query no longer needs to link to genUserProductivity.
//TKS 05.22.2014 #52713 changed this to go back to the old select query and added a second insert into
//activities to log the ticket time. If you logged time on a ticket then click submit to testing or complete
//to both send and stop you time, the activity panel would only show that it was sent to testing and not include the time logged
//on the ticket. This will insert for the time logged then turn around and insert again for the time logged
$query = "select StatusID, Title, Description, TotalTime, StartTime, EndTime
from proTasks left outer join genUserProductivity
on genUserProductivity.TaskID = proTasks.ID
where proTasks.ID=$id
order by EndTime desc limit 1";
//$query = "select StatusID, Title, Description from proTasks where ID= ".$id;
if ( !$result = $db->sql_query( $query ) )
{
LogError( 10983, $query ."<br>".$db->sql_error() );
return false;
}
$activityrow = $db->sql_fetchrow( $result );
if ( $insert_time )
{
$query2 = "insert into activities ( PrimaryID, TypeID, UserID, ActivityDate,
Title,
StartTime, EndTime, Duration ) values ( $id, 9, $UserID, now(),
'".addslashes( $activityrow[Title] )."','$activityrow[StartTime]',
'$activityrow[EndTime]','$activityrow[TotalTime]' )";
if ( !$result2 = $db->sql_query( $query2 ) )
{
LogError( 14700, $query."<br>".$db->sql_error() );
exit;
}
}
if ( $activityrow[ "StatusID" ] == 5 || $activityrow[ "StatusID" ] == 7 )
$type = 7;//completed
else
$type = 8;//testing
//TKS 08.29.2013 #40204 changed start, end and total times to use time stamp and
//add a second to end time and total time for completing or sending a ticket to testing
$activitiesquery = "insert into activities ( PrimaryID, TypeID, UserID, ActivityDate,
Title,
StartTime, EndTime, Duration ) values ( $id, $type, $UserID, now(),
'".addslashes( $activityrow[Title] )."', utc_timestamp(),
DATE_ADD(UTC_TIMESTAMP(), INTERVAL 1 second ) ,'00:00:01' )";
}
break;
case 2://call
if ( $start_stop == 'start' )
{
$query = "insert into genUserProductivity ( CallLogID, UserID, StartTime, ContactID )
values ( ".$id.", ".$UserID.", UTC_TIMESTAMP, ".$contactid." )";
if ( !$result = $db->sql_query( $query ) )
{
LogError( 8777, $query ."<br>".$db->sql_error() );
return false;
}
}
if ( $start_stop == 'stop' )
{
//11.14.2014 ghh - changed query to get its info directly from the call log table because it
//is looking at pause times where this was not
$query = "update genUserProductivity, conCallLogs set genUserProductivity.EndTime = conCallLogs.CallEnded,
genUserProductivity.TotalTime = conCallLogs.TotalTime
where genUserProductivity.CallLogID = ".$id."
and conCallLogs.CallLogID=genUserProductivity.CallLogID
and genUserProductivity.EndTime is NULL and genUserProductivity.UserID = ".$UserID;
if ( !$result = $db->sql_query( $query ) )
{
LogError( 8778, $query ."<br>".$db->sql_error() );
return false;
}
//10.01.2012 ghh - added to deal with logging activiites and tracking call topics and such
//first we need to grab information off the call so we can log it.
$query = "select conCallTopics.CallTopicID, conCallLogs.ContactID, conCallLogs.CallStarted,
conCallLogs.CallEnded, conCallLogs.TotalTime, conCallNotes.SpokeWith,
conCallLogs.Contacted, conCallTopics.TopicName, conCallTopics.Opportunity,
conCallTopics.ProgressLevel, conCallTopics.ProbabilityOfClose,
CloseDate, StartUpAmount, RecurringAmount, SalesRepID,
BusinessName, conCallTopics.Resolved
from conContactInfo,conCallLogs, conCallNotes, conCallTopics
where conCallLogs.CallLogID=$id
and conCallLogs.CallLogID=conCallNotes.CallLogID
and conCallLogs.ContactID=conContactInfo.ContactID
and conCallNotes.CallTopicID=conCallTopics.CallTopicID";
if ( !$result = $db->sql_query( $query ) )
{
LogError( 10910, $query ."<br>".$db->sql_error() );
return false;
}
$callrow = $db->sql_fetchrow( $result );
//TKS 01.13.2015 #62030 I have no idea why there would not be a call topic id but support
//was having issues clocking out. THey were getting a failure on the query below. For now,
//I am taking care of this by checking for a topic id but there is a bigger issue if we have a logid
//above but not result from the query linking to get the topicid.
if ( $callrow[ "CallTopicID" ] > 0 )
{
//TKS 01.15.2014 #46047 now we store sales progress level in a link table. In order to display
//this under each topic on activities panel and not have to query the link table, we update the
//ProgressLevel flag in activities. First we need to check for an open level
$query = "select SalesProcessID from conSalesProgressLink where TopicID = ".$callrow[ "CallTopicID" ]."
and DaysAtThisLevel is null";
if ( !$result = $db->sql_query( $query ) )
{
LogError( 15634, $query ."<br>".$db->sql_error() );
return false;
}
$ProgressLevel = 0;
if ( $db->sql_numrows( $result ) > 0 )
{
$progressrow = $db->sql_fetchrow( $result );
$ProgressLevel = $progressrow[ "SalesProcessID" ];
}
//now build up our insert to place in actvities for the logged call
//note: depending on the information supplied we may be about to work with multiple
//rows in activity
//TKS 05.07.2013 #35202 added secondaryid to hold the calltopicid
$activitiesquery = "insert into activities ( PrimaryID, SecondaryID, TypeID, ContactID, UserID,
ActivityDate, Title,
StartTime, EndTime, Duration, FirstName,
BusinessName, ProgressLevel ) values ( $id, $callrow[CallTopicID], 19, $callrow[ContactID],$UserID, now(),
'".addslashes( $callrow[TopicName] )."','$callrow[CallStarted]',
'$callrow[CallEnded]','$callrow[TotalTime]',
'".addslashes( $callrow[ 'SpokeWith' ] ) ."',
'".addslashes( $callrow[ 'BusinessName' ] ) ."', ".$ProgressLevel." )";
//10.01.2012 ghh - now we need to see if we're dealing with an opportunity and if so
//we need to write it to the table. Noting that its possible the opportunity could
//already exist in the activities panel from a previous call.
if ( $callrow[ 'Opportunity' ] == 1 )
handleOpportunity( $id );
}
}
break;
case 3://invoice
if ( $start_stop == 'start' )
{
$query = "insert into genUserProductivity ( InvoiceID, UserID, StartTime )
values ( ".$id.", ".$UserID.", UTC_TIMESTAMP )";
if ( !$result = $db->sql_query( $query ) )
{
LogError( 8785, $query ."<br>".$db->sql_error() );
return false;
}
}
if ( $start_stop == 'stop' )
{
$query = "update genUserProductivity set EndTime = UTC_TIMESTAMP, TotalTime = sec_to_time( Unix_timestamp( utc_timestamp ) - unix_timestamp( StartTime ) )
where InvoiceID = ".$id." and EndTime is NULL and UserID = ".$UserID;
if ( !$result = $db->sql_query( $query ) )
{
LogError( 8786, $query ."<br>".$db->sql_error() );
return false;
}
//10.04.2012 ghh - added to deal with activites panel
handleInvoice( $id );
}
break;
case 4://job
//TKS 11.18.2013 #43115 no longer assuming viewing user. We now look for the mechanic
//assigned tot he job and start time for them. Else start for viewing user
$query2 = "select ScheduledFor from invJobSchedule where JobID = ".$id;
if ( !$result2 = $db->sql_query( $query2 ) )
{
LogError( 13722, $query2 ."<br>".$db->sql_error() );
return false;
}
$row2 = $db->sql_fetchrow( $result2 );
if ( $row2[ "ScheduledFor" ] > 0 )
$mechanic = $row2[ "ScheduledFor" ];
else
$mechanic = $UserID;
if ( $start_stop == 'start' )
{
$query = "insert into genUserProductivity ( JobID, UserID, StartTime )
values ( ".$id.", ".$mechanic.", UTC_TIMESTAMP )";
if ( !$result = $db->sql_query( $query ) )
{
LogError( 8788, $query ."<br>".$db->sql_error() );
return false;
}
}
if ( $start_stop == 'stop' )
{
$query = "update genUserProductivity set EndTime = UTC_TIMESTAMP, TotalTime = sec_to_time( Unix_timestamp( utc_timestamp ) - unix_timestamp( StartTime ) )
where JobID = ".$id." and EndTime is NULL and UserID = ".$mechanic;
if ( !$result = $db->sql_query( $query ) )
{
LogError( 8787, $query ."<br>".$db->sql_error() );
return false;
}
}
break;
case 5://email
//TKS 11.18.2011 added StartTime so the emails will show up for the date range ;)
$query = "insert into genUserProductivity ( EmailID, UserID, StartTime ) values ( ".$id.", ".$UserID.", utc_timestamp() )";
if ( !$result = $db->sql_query( $query ) )
{
LogError( 8784, $query ."<br>".$db->sql_error() );
return false;
}
handleEmail( $id );
break;
case 6://check list
if ( $start_stop == 'start' )
{
$query = "insert into genUserProductivity ( ChecklistContactID, UserID, StartTime )
values ( ".$id.", ".$UserID.", UTC_TIMESTAMP )";
if ( !$result = $db->sql_query( $query ) )
{
LogError( 8780, $query ."<br>".$db->sql_error() );
return false;
}
}
if ( $start_stop == 'stop' )
{
$query = "update genUserProductivity set EndTime = UTC_TIMESTAMP, TotalTime = sec_to_time( Unix_timestamp( utc_timestamp ) - unix_timestamp( StartTime ) )
where ChecklistContactID = ".$id." and EndTime is NULL and UserID = ".$UserID;
if ( !$result = $db->sql_query( $query ) )
{
LogError( 8783, $query ."<br>".$db->sql_error() );
return false;
}
}
break;
}
//10.01.2012 ghh - added to deal with activities
if ( $activitiesquery != '' )
if ( !$result = $db->sql_query( $activitiesquery ) )
{
LogError( 10908, $query ."<br>".$db->sql_error() );
return false;
}
return true;
}//end of UpdateUserProductivity
<?php
require '../../framework/modules/mysqlModule.php';
require '../../framework/modules/sessionModule.php';
session_start();
switch ($_POST['function']) {
case 'changePic':
handlePicture();
break;
case 'changeName':
handleName();
break;
case 'changeEmail':
handleEmail();
break;
case 'changePassword':
handlePassword();
break;
case 'getCurrentPicture':
getCurrentPicture();
break;
case 'getAllPictures':
getAllPictures();
break;
case 'setAsNewProfilePic':
setAsNewProfilePic();
break;
case 'deletePic':
deletePic();
break;
case 'fetchCurrentValues':