function ProcessTimeSlip($slipid){
//return and aray called $tresults
// Transfer Process is Broken up into 2 sections , if successful on transfering the data to Tigerpaw, it will transfer the information to payroll to be processed.
$query = "SELECT *,
cast(datediff(mi, StartTime, EndTime)/60 as int) as Hours,
datediff(mi, StartTime, EndTime) - (cast(datediff(mi, StartTime, EndTime)/60 as int) * 60) as Minutes
FROM dbo.TimeSlipDetail
INNER JOIN dbo.Employees
ON dbo.Employees.Employenum = dbo.TimeSlipDetail.EmployeNum
WHERE SlipID = '$slipid'
AND Transfered = 'FALSE'";
$qres = mssql_query($query) or die("<status>Unable To Retrieve Associated Details: ".mssql_get_last_message()."</status></dataset></pagedata>");
$count = mssql_num_rows($qres);
if($count==0){
$tresult['TPCode'] = NULL;
$tresult['TPMsg'] = "Time Slip Not Found";
$tresult['TPError'] = TRUE;
return $tresults;
}
for($i=0;$i<$count;$i++){
mssql_select_db("TimeLogsSQL");
$detail = mssql_fetch_assoc($qres);
mssql_select_db("BBCTest");
$true = 1;
$false = 0;
$null = null;
print_r($detail);
$inttime1 = strtotime($detail['StartTime']);
$inttime2 = strtotime($detail['EndTime']);
$Date1 = date("Y-m-d h:i:000",$inttime1);
$Date2 = date("Y-m-d h:i:000",$inttime1);
echo "$Date1";
/* prepare the statement resource */
$stmt = mssql_init("tsp_CreateSOLog") or die("<status>Unable To Intialize RSP: ".mssql_get_last_message()."</status></dataset></pagedata>");
/* now bind the parameters to it */
mssql_bind($stmt, "@StartDateTime", $Date1, SQLINT4, FALSE);
mssql_bind($stmt, "@EndDateTime", $Date2, SQLVARCHAR, FALSE);
mssql_bind($stmt, "@LogReason", $detail['Job'], SQLVARCHAR, FALSE);
mssql_bind($stmt, "@RepNumber", $detail['TPRep'], SQLINT4, FALSE);
mssql_bind($stmt, "@ActualHours", $detail['Hours'], SQLINT2, FALSE);
mssql_bind($stmt, "@ActualMinutes", $detail['Minutes'], SQLINT2, FALSE);
mssql_bind($stmt, "@Mileage", $detail['Mileage'], SQLFLT8, FALSE);
mssql_bind($stmt, "@SONumber", $detail['SONumber'], SQLINT4, FALSE);
mssql_bind($stmt, "@Billable", $true, SQLBIT, FALSE);
mssql_bind($stmt, "@Chargable", $false, SQLBIT, FALSE);
mssql_bind($stmt, "@LogComment", $null, SQLVARCHAR, FALSE);
mssql_bind($stmt, "@Tolls", $null, SQLFLT8, FALSE);
mssql_bind($stmt, "@Meals", $null, SQLFLT8, FALSE);
mssql_bind($stmt, "@MiscExpenses", $null, SQLFLT8, FALSE);
mssql_bind($stmt, "@Fuel", $null, SQLFLT8, FALSE);
mssql_bind($stmt, "@Parking", $null, SQLFLT8, FALSE);
mssql_bind($stmt, "@Accomodations", $null, SQLFLT8, FALSE);
mssql_bind($stmt, "@LaborRate", $null, SQLFLT8, FALSE);
mssql_bind($stmt, "@SOLogKeyID", $SOLogKeyID, SQLINT4, FALSE);
mssql_bind($stmt, "@ReturnCode", $ReturnCode, SQLINT4, FALSE);
/* now execute the procedure */
$result = mssql_execute($stmt) or die("<status>Unable To Execute RSP: ".mssql_get_last_message()."</status><error>TRUE</error></dataset></pagedata>");
$req = mssql_fetch_row($result);
if($ReturnCode!=0){
$tresult['TPCode'][$i] = $ReturnCode;
$tresult['TPMsg'][$i] = "Time Slip Not Found";
$tresult['TPError'][$i] = TRUE;
}
elseif($ReturnCode==0){
$tresult['TPCode'][$i] = $ReturnCode;
$tresult['TPMsg'][$i] = "Time Slip Not Found";
$tresult['TPError'][$i] = TRUE;
}
else{
$tresult['TPMsg'][$i] = $ReturnCode;
}
echo $ReturnCode;
echo $SOLogKeyID;
unset($stmt);
}
}
/////////////////////////
Here is the Stored Procedure
/////////////////////////
/*********************************************************/
/* File written by SQL Server SysComments Decryptor v1.1 */
/* Copyright (C) 2001 dOMNAR */
/*********************************************************/
USE BBC
go
-----------------------------------------------------------
-- Type: Stored Procedure
-- Name: tsp_CreateSoLog
-----------------------------------------------------------
CREATE PROCEDURE dbo.tsp_CreateSoLog (@SONumber int,
@StartDateTime datetime,
@EndDateTime datetime,
@LogComment varchar(3000),
@LogReason varchar(50),
@RepNumber int,
@ActualHours int,
@ActualMinutes smallint,
@Billable bit,
@Chargable bit,
@Mileage real,
@Tolls money,
@Meals money,
@MiscExpenses money,
@Fuel money,
@Parking money,
@Accomodations money,
@LaborRate money,
@SOLogKeyID int OUTPUT,
@ReturnCode int OUTPUT)
WITH ENCRYPTION AS
DECLARE @ContractNumber int,
@AccountNumber int,
@TotalUnits decimal (10,2),
@UnitFactor float,
@Reason varchar(50),
@Basis varchar(1),
@TotalTime float,
@ElapsedTime int,
@ElapsedHours int,
@ElapsedMinutes smallint,
@OrderDateOpened datetime,
@TestKeyID int,
@AutoOpenOrder bit,
@CountOfLogs int,
@DefaultOpenStatus varchar(50),
@Junk int,
@Junk2 float,
@TotalHours int,
@TotalMinutes int,
@DateClosed datetime,
@LogReasonLaborRate money
/* Return Codes
0 = Successful Add
1 = Missing SO Number
2 = Invalid SO Number
3 = Missing Start DateTime
6 = Have End Date but no Log Reason
7 = End Date/Time < Start Date/Time
8 = Missing/Invalid Rep
9 = Invalid Log Reason
10 = No Log End Date and SO is closed
11 = Have Actual Time but no End date/time
*/
-- initialize return variables
SET @SOLogKeyID = 0
SET @ReturnCode = 0
SET @LogReasonLaborRate = 0
If @LaborRate Is Null SET @LaborRate = 0
IF @ActualHours is Null SET @ActualHours = 0
IF @ActualMinutes is Null SET @ActualMinutes = 0
-- make sure SO # passed in
IF @SONumber = 0
BEGIN
SET @ReturnCode = 1 -- missing SO#
RETURN
END
SET @TestKeyID = Null
-- make sure SO # is valid and get AccountNumber, Contract Number and Date Opened
SELECT @TestKeyID = SONumber, @AccountNumber = AccountNumber,
@ContractNumber = ContractNumber, @OrderDateOpened = DateOpened,
@DateClosed = DateClosed
FROM tblServiceOrders WHERE SONumber = @SONumber
IF @TestKeyID is Null -- Record not found
BEGIN
SET @ReturnCode = 2 -- Invalid SO #
RETURN
END
IF @StartDateTime = 'Dec 30 1899 12:00AM' or @StartDateTime = '' or @StartDateTime = 0 or @StartDateTime is Null
BEGIN
SET @ReturnCode = 3 -- missing start date
RETURN
END
IF @ContractNumber is Null SET @ContractNumber = 0
IF @EndDateTime = 'Dec 30 1899 12:00AM' or @EndDateTime = '' or @EndDateTime = 0 or @EndDateTime is Null
BEGIN -- no end date passed in
-- if Service Order is closed, they can't have a log without an end date/time
IF @DateClosed is Not Null
BEGIN
SET @ReturnCode = 10 -- SO Closed but log has no end date
RETURN
END
IF @ActualHours > 0 OR @ActualMinutes > 0
BEGIN
SET @ReturnCode = 11 -- Have actual time but no end date
RETURN
END
SET @EndDateTime = Null
SET @ActualHours = 0
SET @ActualMinutes = 0
SET @ElapsedHours = 0
SET @ElapsedMinutes = 0
SET @TotalUnits = 0
END
ELSE -- have an end date/time
BEGIN
IF @LogReason is Null or @LogReason = ''
BEGIN
SET @ReturnCode = 6 -- have an end date/time but no Log Reason
RETURN
END
-- make sure End date is not < Start date
IF @EndDateTime < @StartDateTime
BEGIN
SET @ReturnCode = 7 -- End date/time < start date/time
RETURN
END
END
IF @RepNumber = 0
BEGIN
SET @ReturnCode = 8 -- missing rep number
RETURN
END
-- make sure it's a valid rep
SET @TestKeyID = Null
SELECT @TestKeyID = RepNumber FROM tblReps
WHERE RepNumber = @RepNumber
IF @TestKeyID is Null
BEGIN
SET @ReturnCode = 8 -- invalid rep number
RETURN
END
-- calculate elapsed time
IF @EndDateTime is not Null
BEGIN
SET @ElapsedTime = datediff(mi, @StartDateTime, @EndDateTime)
SET @ElapsedHours = cast(@ElapsedTime / 60 as int)
SET @ElapsedMinutes = @ElapsedTime - (@ElapsedHours * 60)
END
ELSE
BEGIN
SET @ElapsedHours = 0
SET @ElapsedMinutes = 0
END
SET @UnitFactor = 0 -- initialize
IF @LogReason is not Null and @LogReason <> ''
-- make sure log reason is valid and get unit factor
BEGIN
SET @Reason = Null
SELECT @Reason = LogReason, @UnitFactor = UnitFactor, @LogReasonLaborRate = StandardRate FROM tblLogReasons
WHERE LogReason = @LogReason
IF @Reason is Null
BEGIN
SET @ReturnCode = 9 -- invalid log reason
RETURN
END
END
IF @LogReasonLaborRate is Null SET @LogReasonLaborRate = 0
-- get total time as a decimal
SET @TotalTime = @ActualHours + (@ActualMinutes / 60.0)
/* NOTE: Must use 60.0 so that the devisor is not an integer otherwise, if both dividend and devisor
are integers, the result will be an integer */
IF @TotalTime > 0
BEGIN
SET @TotalUnits = @TotalTime * @UnitFactor
END
ELSE SET @TotalUnits = 0
IF @LaborRate = 0 SET @LaborRate = @LogReasonLaborRate
INSERT INTO tblSOLogs
(AccountNumber,
SONumber,
StartDateTime,
EndDateTime,
LogComment,
TimeLogReason,
Tech,
LogHours,
LogMinutes,
ElapsedHours,
ElapsedMinutes,
Billable,
Chargable,
ContractNumber,
UnitFactor,
TotalUnits,
Mileage,
Tolls,
Meals,
MiscExpenses,
Fuel,
Parking,
Accomodations,
LogReasonLaborRate)
VALUES
(@AccountNumber,
@SONumber,
@StartDateTime,
@EndDateTime,
@LogComment,
@LogReason,
@RepNumber,
@ActualHours,
@ActualMinutes,
@ElapsedHours,
@ElapsedMinutes,
@Billable,
@Chargable,
@ContractNumber,
@UnitFactor,
@TotalUnits,
@Mileage,
@Tolls,
@Meals,
@MiscExpenses,
@Fuel,
@Parking,
@Accomodations,
@LaborRate)
-- Return the key id to test the success of the insert.
SET @SOLogKeyID = @@Identity
IF @OrderDateOpened is Null -- If the order is not already open
BEGIN
-- check system value to see if first log opens the ticket
SELECT @AutoOpenOrder = AutoOpenOrder FROM tblSysSOSettings
IF @AutoOpenOrder = 1 -- first log opens order
BEGIN
-- see if this is the first log
SELECT @CountOfLogs = COUNT(SOLogKeyID) FROM tblSOLogs
WHERE SONumber = @SONumber
IF @CountOfLogs = 1 -- this is the first log
BEGIN
-- get the default open SO status
SELECT @DefaultOpenStatus = DefaultOpenOrderStatus
FROM tblSysSOSettings
-- Get received time of order and make sure start of log is after the received date
-- open the so with the log start date/time and default open order status
UPDATE tblServiceOrders
SET DateOpened = convert(varchar(25),@StartDateTime, dbo.tf_GetDateFormat()), TimeOpened = convert(varchar(25),@StartDateTime, 8),
Status = @DefaultOpenStatus, StatusChangedIndicator = 1
WHERE SONumber = @SONumber
END
END
END
-- if there is a contract number, see if it is an hourly or units basis and update the contract
IF @ContractNumber > 0
BEGIN
SET @Basis = Null
SELECT @Basis = Basis FROM tblContracts WHERE ContractNumber = @ContractNumber
IF @Basis = 'U' or @Basis = 'H'
BEGIN
EXEC tsp_AdjustContractTime @ContractNumber
END
END
SET @Junk = 0 -- we don't need some of the values returned
SET @Junk2 = 0
SET @TotalHours = 0
SET @TotalMinutes = 0
-- get total time logged against the ticket
EXEC tsp_TallySOLogs @SONumber, 0,
@Junk output, @Junk output, @Junk output, @Junk output,
@Junk output, @Junk output, @TotalHours output, @TotalMinutes output,
@Junk2 output, @Junk2 output, @Junk2 output, @Junk2 output
-- Now update the SO for the new total time logged
UPDATE tblServiceOrders SET TotalHours = @TotalHours, TotalMinutes = @TotalMinutes
WHERE SONumber = @SONumber