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 sql_bridge 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
You could leave a comment if you were logged in.