Sometimes kits have sat in Pharmacy for a while and kit contents of the kit type have changed more than once. These stored procedures fix this and update kit contents of unchecked kits in pharmacy.


N.B. This clears the Batch and Expiry.


CREATE PROCEDURE [dbo].[sp_UpdateKitContents]
  -- Add the parameters for the stored procedure here
@KitID int
AS
BEGIN

  BEGIN TRANSACTION
  -- SET NOCOUNT ON added to prevent extra result sets from
  -- interfering with SELECT statements.
  SET NOCOUNT ON;

  DECLARE @KitType int;

    -- Insert statements for procedure here
  SELECT @KitType = KitTypeId FROM Kit WHERE ID = @KitID

  DECLARE @KitContentDrugs Table(DrugID int)
  INSERT INTO @KitContentDrugs SELECT KitContent.DrugId FROM KitContent JOIN Drug ON Drug.DrugId = KitContent.DrugId WHERE ID = @KitID

  DECLARE @KitTypeDrug Table(DrugID int);
  INSERT INTO @KitTypeDrug SELECT KitType_Drugs.DrugId FROM KitType_Drugs WHERE KitType_Drugs.KitTypeId = @KitType ORDER BY Position;

  SELECT * FROM @KitTypeDrug;

  --Loop Through and Delete AnyThing
  DELETE FROM KitContent WHERE KitId = @KitID;
  
  --Insert Anything thats not its the Kit Type
  INSERT INTO KitContent SELECT @KitID, DrugID,'',''  FROM @KitTypeDrug

  SELECT * FROM KitContent WHERE KitId = @KitID;

  --ROLLBACK;
  COMMIT;
  
END


CREATE PROCEDURE [dbo].[sp_UpdateAllKitsinPharmcyNotChecked]

AS
BEGIN
  -- SET NOCOUNT ON added to prevent extra result sets from
  -- interfering with SELECT statements.
  SET NOCOUNT ON;

    -- Insert statements for procedure here
  --SELECT * FROM Kit WHERE Kit.Checked = 0 AND Kit.LocationId = 1 AND Kit.Deleted = 0 

  DECLARE @KitID INT;

  DECLARE KitLoop CURSOR FAST_FORWARD FOR
    SELECT Kit.ID FROM Kit WHERE Kit.Checked = 0 AND Kit.LocationId = 1 AND Kit.Deleted = 0 --AND Kit.ID BETWEEN @StartID AND @EndID

  OPEN KitLoop
  FETCH NEXT FROM KitLoop INTO @KitID

  WHILE @@FETCH_STATUS = 0
  BEGIN
    SELECT @KitID

    EXEC sp_UpdateKitContents @KitID

    FETCH NEXT FROM KitLoop INTO @KitID
  END

  CLOSE KitLoop

  DEALLOCATE KitLoop

END