Create PROCEDURE [dbo].[InstructionInTheTechnicalAssistant] @Msg_Num TINYINT OUTPUT ,@idHavaleh numeric(19, 0),@ReferType tinyint,@user_Id TINYINT,@TypeOfPrescription TINYINT
AS
DECLARE @CountRow int
set  @msg_num = 1
begin try
begin TRAN
--فراخوانی نسخه های ثبت موقت
IF @ReferType = 0 
BEGIN

SET @CountRow=(SELECT COUNT(Autoid) FROM dbo.TblUMDrugHavaleh WITH (nolock)
WHERE autoid IN (SELECT autoid FROM dbo.TmpDrugHavaleh WITH (nolock) WHERE Id_Havaleh=@idHavaleh ))
--////////////////////////////////////////////////////////////////////////////////////////////////////
IF @CountRow > 0 AND @TypeOfPrescription=0
begin

 DELETE FROM dbo.TblUMDrugHavaleh WHERE   autoid IN (SELECT autoid FROM dbo.TmpDrugHavaleh WITH (nolock) where id_havaleh=@idHavaleh)
INSERT INTO TblUMDrugHavaleh ( autoid,use_code,Mind_code,CreatedBy,TypeOfPrescription,DrugHavalehInstruction,FreeInstruction,HowToUse) 
SELECT autoid,FrequencyCode,RouteCode,@user_Id,@TypeOfPrescription,SUBSTRING(Use_name,1,200)AS note,SUBSTRING(Use_name,1,200)AS note, 
SUBSTRING(routeName,1,200)AS routeName
FROM  TmpDrugHavaleh WITH (nolock)
 LEFT OUTER JOIN (SELECT code,Alias,SUBSTRING(PersianMeaning,1,200)AS routeName
FROM dbo.MedicationTerminology  WITH (nolock) WHERE Type=2
UNION
SELECT Mind_Code,'',Mind_Name  FROM  TblDrugMind WITH (nolock))AS route
  ON route.Code=dbo.TmpDrugHavaleh.RouteCode 
WHERE dbo.TmpDrugHavaleh.Id_Havaleh=@idHavaleh
end
--////////////////////////////////////////////////////////////////////////////////////////////////////
else IF @CountRow > 0 AND @TypeOfPrescription IN (1,2)
  UPDATE dbo.TblUMDrugHavaleh SET use_code=t1.FrequencyCode,Mind_code=t1.RouteCode,
  CreatedBy=@user_Id,DrugHavalehInstruction = t1.note,HowToUse=t1.routeName
  FROM dbo.TblUMDrugHavaleh WITH (NOLOCK)
    INNER JOIN (SELECT Id_Havaleh,autoid,FrequencyCode,RouteCode,SUBSTRING(Use_name,1,200)AS note,SUBSTRING(routeName,1,200)AS routeName
                FROM  TmpDrugHavaleh WITH (nolock)
                 LEFT OUTER JOIN (SELECT code,Alias,SUBSTRING(PersianMeaning,1,200)AS routeName
                  FROM dbo.MedicationTerminology  WITH (nolock) WHERE Type=2
                   UNION
                   SELECT Mind_Code,'',Mind_Name  FROM  TblDrugMind WITH (nolock))AS route
                   ON route.Code=dbo.TmpDrugHavaleh.RouteCode 
                     WHERE dbo.TmpDrugHavaleh.Id_Havaleh=@idHavaleh) AS t1
ON t1.autoid=TblUMDrugHavaleh.autoid

--////////////////////////////////////////////////////////////////////////////////////////////////////
else IF @CountRow = 0 AND @TypeOfPrescription IN (1,2,0)
INSERT INTO TblUMDrugHavaleh ( autoid,use_code,Mind_code,CreatedBy,TypeOfPrescription,DrugHavalehInstruction,FreeInstruction,HowToUse) 
SELECT autoid,FrequencyCode,RouteCode,@user_Id,@TypeOfPrescription,SUBSTRING(Use_name,1,200)AS note,SUBSTRING(Use_name,1,200)AS note, 
SUBSTRING(routeName,1,200)AS routeName
FROM  TmpDrugHavaleh WITH (nolock)
 LEFT OUTER JOIN (SELECT code,Alias,SUBSTRING(PersianMeaning,1,200)AS routeName
FROM dbo.MedicationTerminology  WITH (nolock) WHERE Type=2
UNION
SELECT Mind_Code,'',Mind_Name  FROM  TblDrugMind WITH (nolock))AS route
  ON route.Code=dbo.TmpDrugHavaleh.RouteCode 
WHERE dbo.TmpDrugHavaleh.Id_Havaleh=@idHavaleh




--////////////////////////////////////////////////////////////////////////////////////////////////////

END


--فراخوانی نسخه های صادر شده
ELSE IF  @ReferType = 1 
BEGIN
SET @CountRow=(SELECT COUNT(Autoid) FROM dbo.TblUMDrugHavaleh  WITH (nolock)
WHERE autoid IN (SELECT autoid FROM dbo.DrugHavaleh WITH (nolock) WHERE Id_Havaleh=@idHavaleh ))
--////////////////////////////////////////////////////////////////////////////////////////////////////
IF @CountRow > 0 AND @TypeOfPrescription=0
begin
 DELETE FROM dbo.TblUMDrugHavaleh WHERE   autoid IN (SELECT autoid FROM dbo.DrugHavaleh WITH (nolock) where id_havaleh=@idHavaleh)
 INSERT INTO TblUMDrugHavaleh ( autoid,use_code,Mind_code,CreatedBy,TypeOfPrescription,DrugHavalehInstruction,FreeInstruction,HowToUse) 
SELECT autoid,FrequencyCode,RouteCode,@user_Id,@TypeOfPrescription,SUBSTRING(Note,1,200)AS note,SUBSTRING(Note,1,200)AS note, 
SUBSTRING(routeName,1,200)AS routeName
FROM  dbo.DrugHavaleh WITH (nolock) 
 LEFT OUTER JOIN (SELECT code,Alias,SUBSTRING(PersianMeaning,1,200)AS routeName
FROM dbo.MedicationTerminology  WITH (nolock) WHERE Type=2
UNION
SELECT Mind_Code,'',Mind_Name  FROM  TblDrugMind WITH (nolock))AS route
  ON route.Code=dbo.DrugHavaleh.RouteCode 
WHERE dbo.DrugHavaleh.Id_Havaleh=@idHavaleh
end
 --////////////////////////////////////////////////////////////////////////////////////////////////////
 else IF @CountRow > 0 AND @TypeOfPrescription IN (1,2)
  --UPDATE dbo.TblUMDrugHavaleh SET use_code=t1.FrequencyCode,Mind_code=t1.RouteCode,
  UPDATE dbo.TblUMDrugHavaleh SET Mind_code=t1.RouteCode,
  CreatedBy=@user_Id,DrugHavalehInstruction = t1.note,HowToUse=t1.routeName
  FROM dbo.TblUMDrugHavaleh WITH (NOLOCK)
    INNER JOIN (SELECT Id_Havaleh,autoid,FrequencyCode,RouteCode,SUBSTRING(note,1,200)AS note,SUBSTRING(routeName,1,200)AS routeName
                FROM  DrugHavaleh WITH (nolock)
                 LEFT OUTER JOIN (SELECT code,Alias,SUBSTRING(PersianMeaning,1,200)AS routeName
                  FROM dbo.MedicationTerminology  WITH (nolock) WHERE Type=2
                   UNION
                   SELECT Mind_Code,'',Mind_Name  FROM  TblDrugMind WITH (nolock))AS route
                   ON route.Code=dbo.DrugHavaleh.RouteCode 
                     WHERE dbo.DrugHavaleh.Id_Havaleh=@idHavaleh) AS t1
ON t1.autoid=TblUMDrugHavaleh.autoid

--////////////////////////////////////////////////////////////////////////////////////////////////////
else IF @CountRow = 0 AND @TypeOfPrescription IN (1,2,0)
INSERT INTO TblUMDrugHavaleh ( autoid,use_code,Mind_code,CreatedBy,TypeOfPrescription,DrugHavalehInstruction,FreeInstruction,HowToUse) 
SELECT autoid,FrequencyCode,RouteCode,@user_Id,@TypeOfPrescription,SUBSTRING(Note,1,200)AS note,SUBSTRING(Note,1,200)AS note, 
SUBSTRING(routeName,1,200)AS routeName
FROM  dbo.DrugHavaleh WITH (nolock) 
 LEFT OUTER JOIN (SELECT code,Alias,SUBSTRING(PersianMeaning,1,200)AS routeName
FROM dbo.MedicationTerminology  WITH (nolock) WHERE Type=2
UNION
SELECT Mind_Code,'',Mind_Name  FROM  TblDrugMind WITH (nolock))AS route
  ON route.Code=dbo.DrugHavaleh.RouteCode 
WHERE dbo.DrugHavaleh.Id_Havaleh=@idHavaleh
--////////////////////////////////////////////////////////////////////////////////////////////////////

END
Set @Msg_Num = 1
commit TRAN
 return @Msg_Num
end try
begin CATCH
  Set @Msg_Num =0
  ROLLBACK TRANSACTION;
  return @Msg_Num
end catch

