CREATE PROCEDURE [dbo].[AddIHIOPrescription] @PrescriptionId BIGINT , @WarehouseCode VARCHAR(3), @TrackingCode INT , @SequenceNumber SMALLINT
AS
	DECLARE @RowIndex INT 
	DECLARE @GoodsCode VARCHAR(15)
	DECLARE @DeliverCount INT 
	DECLARE @DeliverCountCache INT 
	DECLARE @Differ MONEY
	DECLARE @Status TINYINT
	DECLARE @CoveredCount INT 
	DECLARE @InsurerPercent REAL 
	DECLARE @InsurerAmount MONEY
	DECLARE @TotalInsuredAmount MONEY
	DECLARE @TotalInsurerAmount MONEY 
	DECLARE @InsurerAmountCache MONEY 
	DECLARE @BasePrice MONEY 
	DECLARE @CtrMojodi CHAR(1)
	DECLARE @Description NVARCHAR(Max)
	DECLARE @ConsumptionCode VARCHAR(20)
	DECLARE @ConsumptionInstructionCode VARCHAR(20)
	DECLARE @Typeofuse   NVARCHAR(MAX)
	DECLARE @MedicalGradeCode VARCHAR(3)
	DECLARE @MedicalGradeName NVARCHAR(45)
	DECLARE @MedicalGradeStatus NVARCHAR(10)
	Declare @DetailId UNIQUEIDENTIFIER

	DECLARE @CacheData AS TABLE
	(
	Id UNIQUEIDENTIFIER,
	RowIndex INT, 
	InsurerCode VARCHAR(3),
	InsurerName NVARCHAR(50),
	InsuredName NVARCHAR(50),
	InsuredLastName NVARCHAR(50),
	GenderCode TINYINT,  
	BirthDate VARCHAR(10), 
	Mobile VARCHAR(15),
	NationalCode VARCHAR(12),
	Medicalid INT ,
	DoctorName NVARCHAR(100),
	DoctorLastName NVARCHAR(100),
	MedicalGradeCode VARCHAR(3),
	MedicalGradeName NVARCHAR(45),
	MedicalGradeStatus NVARCHAR(10),
	AccountValidTo VARCHAR(10),
	VisitDate VARCHAR(10),
	GoodsCode VARCHAR(15),
	DeliverCount INT,
	Price MONEY ,
	Differ MONEY,
	Status TINYINT,
	CoveredCount INT ,
	InsurerPercent REAL,
	InsurerAmount MONEY,
	TotalInsuredAmount MONEY, 
	TotalInsurerAmount MONEY,     
	BasePrice MONEY ,
	Description NVARCHAR(MAX),
	ConsumptionCode VARCHAR(20),
	ConsumptionInstructionCode VARCHAR(20),
	Typeofuse   NVARCHAR(MAX)
	)
	BEGIN TRY
	BEGIN TRAN 
	SET @DetailId = (Select Top 1 Id From IHIOPrescription Where TrackingCode = @TrackingCode AND SequenceNumber= 
                    CASE
                     WHEN @SequenceNumber = 0 THEN SequenceNumber ELSE @SequenceNumber 
                    END  ORDER BY CreatedOn Desc)
	DELETE FROM @CacheData
	INSERT INTO @CacheData(Id,RowIndex, InsurerCode,InsurerName,InsuredName,InsuredLastName, GenderCode, BirthDate, Mobile,NationalCode,Medicalid, DoctorName, DoctorLastName,
						   AccountValidTo, VisitDate, Description, GoodsCode, DeliverCount, CoveredCount,InsurerPercent
						   ,InsurerAmount,TotalInsuredAmount,TotalInsurerAmount, BasePrice,ConsumptionCode,	ConsumptionInstructionCode,Typeofuse )
    SELECT IHIOPrescription.Id, RowIndex,  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,
	ConsumptionCode,ConsumptionInstructionCode, 
       CASE
	  WHEN ShapeCode IN (0,1,2) THEN   MT.PersianMeaning + CAST(mt4.PersianMeaning AS VARCHAR(4)) + N'عدد'
          WHEN ShapeCode IN (6,3) THEN   MT.PersianMeaning + CAST(NumberOfPeriod AS VARCHAR(4)) + N'عدد'
	  WHEN ShapeCode =8 THEN  MT.PersianMeaning + CAST(NumberOfPeriod AS VARCHAR(4)) + N'پاف'
	  WHEN ShapeCode =9 THEN  MT.PersianMeaning + CAST(NumberOfPeriod AS VARCHAR(4)) + N'مرتبه'
	  WHEN ShapeCode =5 THEN  MT.PersianMeaning + CAST(mt3.PersianMeaning AS VARCHAR(4)) + N'قطره'
	  WHEN ShapeCode =7 THEN  MT.PersianMeaning + CAST(NumberOfPeriod AS VARCHAR(4)) + N'واحد'
	  WHEN ShapeCode=4  THEN  MT.PersianMeaning + N'-' + MT2.PersianMeaning 
      END as Typeofuse  
	FROM dbo.IHIOPrescription WITH (NOLOCK)                                
	INNER JOIN dbo.IHIOPrescriptionDetail WITH (NOLOCK)                    
	ON IHIOPrescriptionDetail.PrescriptionId = IHIOPrescription.Id     
	LEFT OUTER JOIN Sahmiyeh WITH (NOLOCK) ON Sahmiyeh.Sazman_Code = InsurerCode
	LEFT JOIN dbo.MedicationTerminology MT ON dbo.IHIOPrescriptionDetail.ConsumptionCode =MT.Code AND MT.Type=12
    LEFT JOIN dbo.MedicationTerminology MT2 ON    mt2.Code=dbo.IHIOPrescriptionDetail.ConsumptionInstructionCode AND mt2.Type=15
	LEFT OUTER JOIN dbo.MedicationTerminology MT3 ON  mt3.Code=dbo.IHIOPrescriptionDetail.NumberOfPeriodcode AND mt3.Type=(16)
	LEFT OUTER JOIN dbo.MedicationTerminology MT4 ON  mt4.Code=dbo.IHIOPrescriptionDetail.NumberOfPeriodcode AND mt4.Type=(17)
	WHERE  IHIOPrescriptionDetail.PrescriptionId = @DetailId
	
	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, '' MedicalGradeCode, '' MedicalGradeName, '' MedicalGradeStatus, '' AccountValidTo , '' VisitDate, '-1' Description 
	  RETURN
	END   

	SELECT @MedicalGradeCode =  Doctor.Grade_Code, @MedicalGradeName= Grade_Name, 
	@MedicalGradeStatus = 
	CASE
	  WHEN Grade.Status = 1 THEN N'عمومي'
	  WHEN Grade.Status = 2 THEN N'متخصص'
	  WHEN Grade.Status = 3 THEN N'فوق تخصص'
	  WHEN Grade.Status = 4 THEN N'ماما'
	  WHEN Grade.Status = 5 THEN N'دندانپزشک'
	  WHEN Grade.Status = 6 THEN N'اتباع'
	END 
	FROM dbo.Doctor INNER JOIN dbo.Grade ON Grade.Grade_Code = Doctor.Grade_Code
	WHERE Doctor.Nezam_No = (SELECT TOP 1 CAST(Medicalid As VARCHAR(10)) FROM @CacheData)
	UPDATE @CacheData SET MedicalGradeCode = @MedicalGradeCode, MedicalGradeName = @MedicalGradeName, MedicalGradeStatus = @MedicalGradeStatus

	DECLARE CursorIHIOPrescription CURSOR FOR
	SELECT GoodsCode,DeliverCount,ISNULL(CoveredCount,0) CoveredCount,InsurerPercent, InsurerAmount, TotalInsuredAmount, TotalInsurerAmount, BasePrice,
	Description,ConsumptionCode,ConsumptionInstructionCode,Typeofuse
	FROM @CacheData
	ORDER BY RowIndex

    OPEN CursorIHIOPrescription
    FETCH NEXT FROM CursorIHIOPrescription INTO @GoodsCode,@DeliverCount,@CoveredCount,@InsurerPercent, @InsurerAmount, @TotalInsuredAmount, @TotalInsurerAmount, @BasePrice,
	@Description,@ConsumptionCode,@ConsumptionInstructionCode,@Typeofuse
	SET @RowIndex = 1
    WHILE @@FETCH_STATUS = 0 
    BEGIN
	   Exec  @CtrMojodi = ChkCtrMojodi @GoodsCode , @WarehouseCode, 0  
	   IF (@TotalInsurerAmount > 0 AND @DeliverCount* @InsurerAmount > 0) 
		 IF @BasePrice >= @InsurerAmount
		   SET @InsurerPercent = 100 - (@TotalInsurerAmount * 100/(@CoveredCount* @InsurerAmount))
		 ELSE
           SET @InsurerPercent = 100 - (@TotalInsurerAmount * 100/(@CoveredCount* @BasePrice))           
	   ELSE 
		 SET @InsurerPercent = 100

	   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)
		   IF @InsurerPercent = 0
		     SET @Status = 4
		   ELSE IF @InsurerPercent = 100
		     SET @Status = 0
		   ELSE 
  		     SET @Status = 6
		 END 
		 ELSE
		 BEGIN
		   IF @InsurerPercent = 0
		     SET @Status = 4
		   ELSE IF @InsurerPercent = 100
		     SET @Status = 0
		   ELSE 
		     SET @Status = 5
		   SET @Differ = 0
		 END 
	   END  
	   ELSE
	   BEGIN
		 SET @InsurerAmountCache = @BasePrice
		 SET @Status = 0
	   END 
	   SET @InsurerPercent = CAST(CAST(@InsurerPercent as DECIMAL(9,2)) AS REAL)
	   INSERT INTO dbo.TmpDrugHavaleh(Id_Havaleh, Radif, K_code, Sender, Reciver, K_Qty1,Price_Forosh, CtrMojodi,Serial_Flag,Make_Flag,Hamrah_Flag ,Tot_Differ,
	   BimarPercent, Status, Note,FrequencyCode,RouteCode,Use_Name, TypeOfMedicine, Price_Forosh_O, Price_Sazman_O, Main_Price)
	   VALUES(@PrescriptionId, @RowIndex, @GoodsCode, @WarehouseCode, '10000', @DeliverCountCache, @InsurerAmountCache, @CtrMojodi,1,0,0, @Differ,
	   @InsurerPercent,  @Status, @Description,@ConsumptionCode,@ConsumptionInstructionCode,@Typeofuse,1, @InsurerAmountCache + @Differ/@DeliverCountCache, @InsurerAmountCache,@InsurerAmountCache + @Differ/@DeliverCountCache )

	   SET @DeliverCountCache = @DeliverCount
	   SET @InsurerAmountCache = @InsurerAmount
       SET @RowIndex = @RowIndex + 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,Make_Flag,Hamrah_Flag ,Tot_Differ, BimarPercent, Status, Note,FrequencyCode,RouteCode,Use_Name, TypeOfMedicine, Price_Forosh_O, Price_Sazman_O, Main_Price)
	     VALUES(@PrescriptionId, @RowIndex, @GoodsCode, @WarehouseCode, '10000', @DeliverCountCache, @InsurerAmountCache, @CtrMojodi,1,0,0,  @Differ, @InsurerPercent,  @Status, @Description,@ConsumptionCode,@ConsumptionInstructionCode,@Typeofuse,1,0,0,0)
         SET @RowIndex = @RowIndex + 1	     
	   END 
       FETCH NEXT FROM CursorIHIOPrescription INTO @GoodsCode,@DeliverCount,@CoveredCount,@InsurerPercent, @InsurerAmount, @TotalInsuredAmount, @TotalInsurerAmount, @BasePrice,@Description,@ConsumptionCode,@ConsumptionInstructionCode,@Typeofuse
    END 
	CLOSE CursorIHIOPrescription
	DEALLOCATE CursorIHIOPrescription
	COMMIT TRAN
	SELECT TOP 1 Id, InsurerCode, InsurerName,InsuredName, InsuredLastName,GenderCode,BirthDate, Mobile, 
	NationalCode,Medicalid, DoctorName, DoctorLastName, MedicalGradeCode, MedicalGradeName, MedicalGradeStatus,   AccountValidTo , VisitDate, 'Success' Description,ConsumptionCode,ConsumptionInstructionCode,Typeofuse 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, '' ,'' MedicalGradeCode, '' MedicalGradeName, '' MedicalGradeStatus,  '' AccountValidTo , '' VisitDate, '-2' Description 
	END CATCH
