CREATE PROCEDURE AddIHIOPrescription @PrescriptionId Bigint, @WarehouseCode VARCHAR(3) , @TrackingCode INT 
 AS 

	DECLARE @GoodsCode VARCHAR(15)
	DECLARE @DeliverCount INT 
	DECLARE @DeliverCountCache INT 
	DECLARE @Differ MONEY
	DECLARE @Status TINYINT
	DECLARE @CoveredCount INT 
	DECLARE @InsurerPercent INT 
	DECLARE @InsurerAmount MONEY
	DECLARE @TotalInsuredAmount MONEY
	DECLARE @TotalInsurerAmount MONEY 
	DECLARE @InsurerAmountCache MONEY 
	DECLARE @BasePrice MONEY 
	DECLARE @Row INT = 1
	DECLARE @CtrMojodi CHAR(1)
	DECLARE @Description NVARCHAR(Max)
 

	DECLARE @CacheData AS TABLE
	(
	Id UNIQUEIDENTIFIER,
	InsurerCode VARCHAR(3),
	InsurerName NVARCHAR(25),
	InsuredName NVARCHAR(30),
	InsuredLastName NVARCHAR(50),
	GenderCode TINYINT,  
	BirthDate VARCHAR(10), 
	Mobile VARCHAR(11),
	NationalCode VARCHAR(10),
	Medicalid INT ,
	DoctorName NVARCHAR(30),
	DoctorLastName NVARCHAR(50),
	AccountValidTo VARCHAR(10),
	VisitDate VARCHAR(10),
	GoodsCode VARCHAR(15),
	DeliverCount INT,
	Price MONEY ,
	Differ MONEY,
	Status TINYINT,
	CoveredCount INT ,
	InsurerPercent INT,
	InsurerAmount MONEY,
	TotalInsuredAmount MONEY, 
	TotalInsurerAmount MONEY,     
	BasePrice MONEY ,
	Description NVARCHAR(MAX)
	)
	BEGIN TRY
	BEGIN TRAN 

	INSERT INTO @CacheData(Id,InsurerCode,InsurerName,InsuredName,InsuredLastName, GenderCode, BirthDate, Mobile,NationalCode,Medicalid, DoctorName, DoctorLastName,
						   AccountValidTo, VisitDate, Description, GoodsCode, DeliverCount, CoveredCount,InsurerPercent,InsurerAmount,TotalInsuredAmount,TotalInsurerAmount, BasePrice)

	SELECT IHIOPrescription.Id,  InsurerCode, Sa_Name InsurerName,InsuredName, InsuredLastName,
	Case When GenderCode in (0,2) Then 1 Else 2 End As GenderCode,
	Dbo.MiladiToShamsi(BirthDate) BirthDate, Mobile, NationalCode,
	Medicalid, DoctorName, DoctorLastName, Dbo.MiladiToShamsi(AccountValidTo) AccountValidTo , 
	Dbo.MiladiToShamsi(VisitDate) VisitDate,Description,
	GoodsCode,DeliverCount,ISNULL(CoveredCount,0) CoveredCount,InsurerPercent, InsurerAmount, TotalInsuredAmount, TotalInsurerAmount, BasePrice
	FROM dbo.IHIOPrescription WITH (NOLOCK)                                
	INNER JOIN dbo.IHIOPrescriptionDetail WITH (NOLOCK)                    
	ON IHIOPrescriptionDetail.PrescriptionId = IHIOPrescription.Id     
	INNER JOIN dbo.Sahmiyeh WITH (NOLOCK) ON Sahmiyeh.Sazman_Code = InsurerCode
	WHERE TrackingCode = @TrackingCode

	IF (SELECT COUNT(0) FROM @CacheData) = 0   
	BEGIN
	  ROLLBACK TRAN
	  SELECT  NEWID() Id, '' InsurerCode, '' InsurerName,'' InsuredName, '' InsuredLastName,2 GenderCode,'' BirthDate, '' Mobile, 
	          '' NationalCode,0 Medicalid, '' DoctorName, '' DoctorLastName, '' AccountValidTo , '' VisitDate, '-1' Description 
	  RETURN
	END   

	DECLARE CursorIHIOPrescription CURSOR FOR
	SELECT GoodsCode,DeliverCount,ISNULL(CoveredCount,0) CoveredCount,InsurerPercent, InsurerAmount, TotalInsuredAmount, TotalInsurerAmount, BasePrice, Description
	FROM @CacheData

    OPEN CursorIHIOPrescription
    FETCH NEXT FROM CursorIHIOPrescription INTO @GoodsCode,@DeliverCount,@CoveredCount,@InsurerPercent, @InsurerAmount, @TotalInsuredAmount, @TotalInsurerAmount, @BasePrice,@Description
    WHILE @@FETCH_STATUS = 0 
    BEGIN
	   Exec  @CtrMojodi = ChkCtrMojodi @GoodsCode , @WarehouseCode, 0  
	   IF @TotalInsurerAmount > 0 
		 SET @InsurerPercent = 100 - @InsurerPercent
	   ELSE 
		 SET @InsurerPercent = 0

	   SET @DeliverCountCache = @DeliverCount
	   SET @InsurerAmountCache = @InsurerAmount
	   SET @Differ = 0

	   IF @CoveredCount > 0 
	   BEGIN
		 SET @DeliverCountCache = @CoveredCount
		 IF @InsurerAmountCache > @BasePrice 
		   SET @InsurerAmountCache = @BasePrice
		 Else  
		   SET @InsurerAmountCache = @InsurerAmount

		 IF @BasePrice > @InsurerAmountCache
		 BEGIN 
		   SET @Differ = @CoveredCount * (@BasePrice - @InsurerAmountCache)
		   SET @Status = 6
		 END 
		 ELSE
		 BEGIN
		   SET @Status = 5
		   SET @Differ = 0
		 END 
	   END  
	   ELSE
	   BEGIN
		 SET @InsurerAmountCache = @BasePrice
		 SET @Status = 0
	   END 
	   INSERT INTO dbo.TmpDrugHavaleh(Id_Havaleh, Radif, K_code, Sender, Reciver, K_Qty1,Price_Forosh, CtrMojodi,Serial_Flag, Tot_Differ, BimarPercent, Status, Note)
	   VALUES(@PrescriptionId, @Row, @GoodsCode, @WarehouseCode, '10000', @DeliverCountCache, @InsurerAmountCache, @CtrMojodi, 0, @Differ, @InsurerPercent,  @Status, @Description)

	   SET @DeliverCountCache = @DeliverCount
	   SET @InsurerAmountCache = @InsurerAmount
       SET @Row = @Row + 1	     

	   IF @CoveredCount > 0 AND @DeliverCountCache > @CoveredCount
	   BEGIN
		 SET @DeliverCountCache = @DeliverCountCache - @CoveredCount
		 SET @InsurerAmountCache = @BasePrice
		 SET @Status = 0
		 SET @InsurerPercent = 0
		 SET @Differ = 0 
	     INSERT INTO dbo.TmpDrugHavaleh(Id_Havaleh, Radif, K_code, Sender, Reciver, K_Qty1,Price_Forosh, CtrMojodi,Serial_Flag, Tot_Differ, BimarPercent, Status, Note)
	     VALUES(@PrescriptionId, @Row, @GoodsCode, @WarehouseCode, '10000', @DeliverCountCache, @InsurerAmountCache, @CtrMojodi, 0, @Differ, @InsurerPercent,  @Status, @Description)
         SET @Row = @Row + 1	     
	   END 
       FETCH NEXT FROM CursorIHIOPrescription INTO @GoodsCode,@DeliverCount,@CoveredCount,@InsurerPercent, @InsurerAmount, @TotalInsuredAmount, @TotalInsurerAmount, @BasePrice,@Description
    END 
	CLOSE CursorIHIOPrescription
	DEALLOCATE CursorIHIOPrescription
	COMMIT TRAN
	SELECT TOP 1 Id, InsurerCode, InsurerName,InsuredName, InsuredLastName,GenderCode,BirthDate, Mobile, 
	NationalCode,Medicalid, DoctorName, DoctorLastName, AccountValidTo , VisitDate, 'Success' Description FROM @CacheData
	END TRY
	BEGIN CATCH
	  IF CURSOR_STATUS('global','CursorIHIOPrescription') = 1
	  BEGIN
		CLOSE CursorIHIOPrescription
		DEALLOCATE CursorIHIOPrescription
	  END
	  DECLARE @ErrorMessage NVARCHAR(MAX), @ErrorSeverity INT, @ErrorState INT;
	  SELECT @ErrorMessage = ERROR_MESSAGE() + ' Line ' + CAST(ERROR_LINE() AS NVARCHAR(5)), @ErrorSeverity = ERROR_SEVERITY(), @ErrorState = ERROR_STATE();
	  ROLLBACK TRANSACTION;
	  RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);
	  SELECT  NEWID() Id, '' InsurerCode, '' InsurerName,'' InsuredName, '' InsuredLastName,2 GenderCode,'' BirthDate, '' Mobile, 
	          '' NationalCode,0 Medicalid, '' DoctorName, '' DoctorLastName, '' AccountValidTo , '' VisitDate, '-2' Description 
	END CATCH


