{$page}
This function demonstrates inserting Time Clock entries (in this case a vacation record) directly into the Control database. The insert requires 2 records in the Journal and 2 records in the TimeCard database. (A summary/detail for each.)
WARNING: If you are interested in using SQL Bridge, please contact a sales or implementation consultant at Cyrious.
Overview
Cyrious Control's SQLBridge is a collection of SQL Server stored procedures and functions and external modules that can be called to safely insert or update data into the Control database and notify the CHAPI service that data has been updated. The CHAPI service is a controller program that will then notify the SSLIP and all copies of Control about the changes.
Caution: Use of this feature requires expertise in SQL Server and in the Cyrious database structure. It is very possible to irreversibly damage your Cyrious Control database if you make a mistake. Always develop your tests using a separate database on a separate machine.
Caution: Always wrap multi-record operations with SQL transaction statements. This ensure the integrity of the entire update by preventing partial changes or records from being saved. If you are not familiar with using SQL transactions, STOP! Do not proceed with any database insertions, deletions, or updates until you are.
Example
This example inserts a note into a customer record.
DECLARE @MasterJournalID INT;
DECLARE @DetailJournalID INT;
DECLARE @EmployeeID INT;
DECLARE @MasterClassTypeID INT;
DECLARE @DetailClassTypeID INT;
DECLARE @DT smalldatetime;
DECLARE @StartTime smalldatetime;
DECLARE @EndTime smalldatetime;
DECLARE @TimeClockStationID INT;
DECLARE @TimeClockStation varchar(255);
DECLARE @DivisionID INT;
DECLARE @ErrorMessage VARCHAR(2048);
DECLARE @ErrorNumber INT;
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;
DECLARE @ErrorLine INT;
DECLARE @ErrorProcedure VARCHAR(200);
– Step 0 – Set the key variables
SET @EmployeeID = 1036; – Look this up from the input parameter
SET @DivisionID = 10;
SET @TimeClockStationID = 1261;
SET @TimeClockStation = (select StationName from Station where ID = @TimeClockStationID);
SET @MasterClassTypeID = 20050; – Master Time Card
SET @DetailClassTypeID = 20051; – Detail Time Card
SET @DT = convert(date, getdate());
SET @StartTime = dateadd(hour, 9, @DT);
SET @EndTime = dateadd(hour, 17, @DT);
print @StartTime;
print @EndTime;
print @TimeClockStation;
print convert(datetime, convert(float, @EndTime) - convert(float, @StartTime))
– Step 2, Get the new Journal IDs
SET @MasterJournalID = (SELECT dbo.csf_chapi_nextid(@MasterClassTypeID, 2));
SET @DetailJournalID = (SELECT dbo.csf_chapi_nextid(@DetailClassTypeID, 2));
– Step 3, Insert Records!
BEGIN TRANSACTION
BEGIN TRY
- - insert master journal record
INSERT INTO [Journal] ([ID], [StoreID], [ClassTypeID] ,[ModifiedByUser] ,[ModifiedByComputer] ,[ModifiedDate] ,[SeqID] ,[IsSystem] ,[IsActive] ,[EmployeeID],
[JournalActivityType] ,[JournalActivityText] ,[Description] ,[Notes] ,[StartDateTime] ,[EndDateTime] ,[TotalTime] ,[ScheduledDateTime] ,[CompletedByID] ,
[CompletedDateTime] ,[IsSummary] ,[IsDetail] ,[SummaryID] ,[SummaryClassTypeID] ,[SummaryAmount] ,[DetailAmount] ,[StartGLGroupID] ,[EndGLGroupID] ,
[AccountID] ,[AccountClassTypeID] ,[ContactID] ,[ContactClassTypeID] ,[TransactionID] ,[TransactionClassTypeID] ,[IsVoided] ,[VoidedDateTime] ,
[VoidedEntryID] ,[VoidedEntryClassTypeID] ,[VoidedReason] ,[QueryStartDateTime] ,[QueryEndDateTime] ,[ReminderDateTime] ,[ReminderPrompt] ,[PartID] ,
[ActivityType] ,[ActivityTypeText] ,[IsBillable] ,[BillableDateTime] ,[UseActualTime] ,[BillingNotes] ,[BillingType] ,[TotalBilledTime] ,
[RecurringActivityID] ,[LinkID] ,[LinkStoreID] ,[LinkClassTypeID] ,[SpecialCode] ,[DivisionID] ,[HasCalendarLinks] ,[TipRecipientID] ,[PartClassTypeID] ,
[RecurringClassTypeID] ,[StationID] ,[StationClassTypeID] ,[CurrentState] ,[StageID] ,[StageClassTypeID])
VALUES
( @MasterJournalID -- (
, -1 -- ,
, @MasterClassTypeID -- ,
, 'SQLBridge' -- ,
, @@ServerName -- ,
, GetDate() -- ,
, 0 -- ,
, 0 -- ,
, 1 -- ,
, @EmployeeID --
, 1 --
, 'Time Card' --
, 'Master Time Card (9:00 am - 5:00 pm)' --
, NULL --
, @StartTime --
, @EndTime --
, convert(datetime, convert(float, @EndTime) - convert(float, @StartTime)) --
, NULL --
, @EmployeeID --
, @EndTime --
, 1 --
, 0 --
, NULL --
, NULL --
, NULL --
, NULL --
, NULL --
, NULL --
, NULL --
, NULL --
, NULL --
, NULL --
, NULL --
, NULL --
, 0 --
, NULL --
, NULL --
, NULL --
, NULL --
, @StartTime --
, @EndTime --
, NULL --
, 0 --
, NULL --
, 0 --
, NULL --
, 0 --
, NULL --
, 0 --
, NULL --
, 0 --
, 0 --
, NULL --
, NULL --
, NULL --
, NULL --
, NULL --
, @DivisionID --
, 0 --
, NULL --
, NULL --
, NULL --
, NULL --
, NULL --
, NULL --
, NULL --
, NULL -- )
);
- - insert into master timecard record
INSERT INTO [TimeCard] ([ID], [StoreID], [ClassTypeID], [ModifiedByUser], [ModifiedByComputer], [ModifiedDate], [SeqID], [IsSystem], [IsActive],
[StatusID], [StatusName], [AdjustedByID], [ManuallyAdjusted], [TransDetailID], [TransDetailStoreID], [TransDetailClassTypeID], [TransPartID],
[TransPartStoreID], [TransPartClassTypeID], [PartUsageCardID], [StationID], [StationClassTypeID], [StationName], [StraightTime], [OverTime],
[DoubleTime], [ShiftDiffTime], [CanCreatePartUsageCard])
VALUES
( @MasterJournalID -- (
, -1 -- ,
, @MasterClassTypeID -- ,
, 'SQLBridge' -- ,
, @@ServerName -- ,
, GetDate() -- ,
, 0 -- ,
, 0 -- ,
, 1 -- ,
, NULL --
, NULL --
, NULL --
, 0 --
, NULL --
, NULL --
, NULL --
, NULL --
, NULL --
, NULL --
, NULL --
, NULL --
, NULL --
, NULL --
, 8 --
, 0 --
, 0 --
, 0 --
, 0 --
);
- - insert detail journal record
INSERT INTO [Journal] ([ID], [StoreID], [ClassTypeID] ,[ModifiedByUser] ,[ModifiedByComputer] ,[ModifiedDate] ,[SeqID] ,[IsSystem] ,[IsActive] ,[EmployeeID],
[JournalActivityType] ,[JournalActivityText] ,[Description] ,[Notes] ,[StartDateTime] ,[EndDateTime] ,[TotalTime] ,[ScheduledDateTime] ,[CompletedByID] ,
[CompletedDateTime] ,[IsSummary] ,[IsDetail] ,[SummaryID] ,[SummaryClassTypeID] ,[SummaryAmount] ,[DetailAmount] ,[StartGLGroupID] ,[EndGLGroupID] ,
[AccountID] ,[AccountClassTypeID] ,[ContactID] ,[ContactClassTypeID] ,[TransactionID] ,[TransactionClassTypeID] ,[IsVoided] ,[VoidedDateTime] ,
[VoidedEntryID] ,[VoidedEntryClassTypeID] ,[VoidedReason] ,[QueryStartDateTime] ,[QueryEndDateTime] ,[ReminderDateTime] ,[ReminderPrompt] ,[PartID] ,
[ActivityType] ,[ActivityTypeText] ,[IsBillable] ,[BillableDateTime] ,[UseActualTime] ,[BillingNotes] ,[BillingType] ,[TotalBilledTime] ,
[RecurringActivityID] ,[LinkID] ,[LinkStoreID] ,[LinkClassTypeID] ,[SpecialCode] ,[DivisionID] ,[HasCalendarLinks] ,[TipRecipientID] ,[PartClassTypeID] ,
[RecurringClassTypeID] ,[StationID] ,[StationClassTypeID] ,[CurrentState] ,[StageID] ,[StageClassTypeID])
VALUES
( @MasterJournalID+1 -- (
, -1 -- ,
, @MasterClassTypeID -- ,
, 'SQLBridge' -- ,
, @@ServerName -- ,
, GetDate() -- ,
, 0 -- ,
, 0 -- ,
, 1 -- ,
, @EmployeeID --
, 1 --
, 'Time Card' --
, @TimeClockStation + ' Time Card ' --
, NULL --
, @StartTime --
, @EndTime --
, convert(datetime, convert(float, @EndTime) - convert(float, @StartTime)) --
, NULL --
, @EmployeeID --
, @EndTime --
, 0 --
, 1 --
, @MasterJournalID --
, @MasterClassTypeID --
, NULL --
, NULL --
, NULL --
, NULL --
, NULL --
, NULL --
, NULL --
, NULL --
, NULL --
, NULL --
, 0 --
, NULL --
, NULL --
, NULL --
, NULL --
, @StartTime --
, @EndTime --
, NULL --
, 0 --
, NULL --
, 0 --
, NULL --
, 0 --
, NULL --
, 0 --
, NULL --
, 0 --
, 0 --
, NULL --
, NULL --
, NULL --
, NULL --
, NULL --
, @DivisionID --
, 0 --
, NULL --
, NULL --
, NULL --
, @TimeClockStationID --
, 26100 --
, NULL --
, NULL --
, NULL -- )
);
- - insert into detail timecard record
INSERT INTO [TimeCard] ([ID], [StoreID], [ClassTypeID], [ModifiedByUser], [ModifiedByComputer], [ModifiedDate], [SeqID], [IsSystem], [IsActive],
[StatusID], [StatusName], [AdjustedByID], [ManuallyAdjusted], [TransDetailID], [TransDetailStoreID], [TransDetailClassTypeID], [TransPartID],
[TransPartStoreID], [TransPartClassTypeID], [PartUsageCardID], [StationID], [StationClassTypeID], [StationName], [StraightTime], [OverTime],
[DoubleTime], [ShiftDiffTime], [CanCreatePartUsageCard])
VALUES
( @MasterJournalID+1 -- (
, -1 -- ,
, @MasterClassTypeID -- ,
, 'SQLBridge' -- ,
, @@ServerName -- ,
, GetDate() -- ,
, 0 -- ,
, 0 -- ,
, 1 -- ,
, NULL --
, NULL --
, NULL --
, 0 --
, NULL --
, NULL --
, NULL --
, NULL --
, NULL --
, NULL --
, NULL --
, @TimeClockStationID --
, 26100 --
, @TimeClockStation --
, 8 --
, 0 --
, 0 --
, 0 --
, 0 --
);
– Now commit the Transaction
COMMIT TRANSACTION
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION;
SELECT @ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE();
- - Use RAISERROR inside the CATCH block to return
- - error information about the original error that
- - caused execution to jump to the CATCH block.
RAISERROR (@ErrorMessage, -- Message text.
@ErrorSeverity, -- Severity.
@ErrorState -- State.
);
END CATCH;
– Step 5, Nothing to refresh
– EXEC dbo.csf_chapi_refresh @ID, @ctID, 0;
Source
Contributor: Cyrious Software
Date: 2/2016
Version: Control 5.7
See Also
- Backlinks include_pagepage_componentbacklinks