Cyrious Control's sql_bridge is a collection of SQL Server stored prodedures 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.

This example inserts a scheduled payment into the database for a series of order (based on a query).

-- Step 0, Variable Declarations
DECLARE @ScheduledDate smalldatetime;
DECLARE @COUNT INT;
DECLARE @NextID INT;
DECLARE @ClassTypeID INT;
DECLARE @Orders TABLE (THID INT PRIMARY KEY);
DECLARE @ErrorMessage    VARCHAR(2048);
DECLARE @ErrorNumber     INT;
DECLARE @ErrorSeverity   INT;
DECLARE @ErrorState      INT;
DECLARE @ErrorLine       INT;
DECLARE @ErrorProcedure  VARCHAR(200);
-- Step 1, Set any user-specified data
SET @ScheduledDate = '2/10/2016';
SET @ClassTypeID = 16110; -- ClassTypeID for Scheduled Payments
-- Step 2, figure Out the orders to create scheduled payments for.
--     Adjust the WHERE clause for your needs but leave the last line (so it doesn't duplicate existing scheduled payments)
--
INSERT INTO @Orders
	SELECT TH.ID 
	FROM TransHeader TH
	WHERE StatusID = 3 
	AND EXISTS (SELECT * FROM TransDetail TD WHERE TD.TransHeaderID = TH.ID 
						AND TD.GoodsItemID IN (1407, 1408, 10016, 10017, 10019, 1441, 1442, 1443, 1444, 1445, 1446, 1447) )
	AND NOT EXISTS (SELECT * FROM ScheduledPayment Existing WHERE Existing.TransactionID = TH.ID) 
	ORDER BY ID
 
SET @COUNT = (SELECT COUNT(*) FROM @Orders)
-- Step 3, Get the starting ID and reserve @Count IDs
SET @NextID = (SELECT dbo.csf_chapi_nextid(@ClassTypeID, @COUNT));
-- Step 4, Insert Records!
BEGIN TRANSACTION
   BEGIN TRY
	 INSERT INTO dbo.ScheduledPayment
	 	(
	 	ID, StoreID, ClassTypeID, ModifiedByUser, ModifiedByComputer, ModifiedDate, 
	 	SeqID, IsSystem, IsActive, TransactionID, TransactionNumber, ScheduledDate, 
	 	Description, Amount, Attempts, Results, IsComplete, CompletedDate, 
	 	CompletedPaymentID
	 	)
	SELECT  @NextID - 1 + rank() OVER (ORDER BY TH.ID) AS ID
			, CONVERT(INT, -1) AS StoreID
			, @ClassTypeID AS ClassTypeID
			, 'SQLBridge' AS ModifiedByUser
			, CONVERT(VARCHAR(12), NULL) AS ModifiedByComputer
			, getdate() AS ModifiedDate
			, CONVERT(INT, 0) AS SeqID
			, CONVERT(bit, 1) AS IsSystem
			, CONVERT(bit, 1) AS IsActive
			, TH.ID AS TransactionID
			, TH.OrderNumber AS TransactionNumber
			, @ScheduledDate AS ScheduledDate
			, 'Payment for Order #'+CONVERT(VARCHAR(10), TH.TransactionNumber) + ' ' + TH.Description AS Description
			, TH.BalanceDue AS Amount
			, CONVERT(INT, 0) AS Attempts
			, '' AS Results
			, CONVERT(bit, 0) AS IsComplete
			, CONVERT(smalldatetime, NULL) AS CompletedDate
			, CONVERT(INT, NULL) AS CompletedPaymentID
	FROM @Orders O 
	JOIN TransHeader TH ON O.THID = TH.ID
	-- 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, Refresh the Orders. 
--		Rather than use csf_chapi_refersh (which would be fine)
--		I'm going to insert records in the RefreshMonitor table.
--		This will take 1 minute or so to refresh (not as good),
--		but it's a whole lot easier.
	INSERT INTO RefreshMonitor (ID, ClassTypeID, SeqID, IsDeleted)
		SELECT O.THID, @ClassTypeID, -1, CONVERT(bit, 0)
		FROM @Orders O

Contributor: Cyrious Software

Date: 2/2016

Version: Control 5.7