
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 @IsLock TINYINT
	DECLARE @InsurerCode VARCHAR(5)
	DECLARE @PurchasePrice MONEY

	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),
	Price_Kharid MONEY 
	)
	BEGIN TRY
	BEGIN TRAN 
	SET @DetailId = (Select Top 1 Id From IHIOPrescription WITH (NOLOCK) 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, Price_Kharid)
    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, IHIOPrescriptionDetail.TotalInsuredAmount, IHIOPrescriptionDetail.TotalInsurerAmount, BasePrice,
	ConsumptionCode,ConsumptionInstructionCode,
       CASE
	  WHEN ShapeCode IN (0,1,2) THEN   MT.PersianMeaning + CAST(mt4.PersianMeaning AS VARCHAR(50)) + N'عدد'
          WHEN ShapeCode IN (6,3) THEN   MT.PersianMeaning + CAST(NumberOfPeriod AS VARCHAR(50)) + N'عدد'
	  WHEN ShapeCode =8 THEN  MT.PersianMeaning + CAST(NumberOfPeriod AS VARCHAR(50)) + N'پاف'
	  WHEN ShapeCode =9 THEN  MT.PersianMeaning + CAST(NumberOfPeriod AS VARCHAR(50)) + N'مرتبه'
	  WHEN ShapeCode =5 THEN  MT.PersianMeaning + CAST(mt3.PersianMeaning AS VARCHAR(50)) + N'قطره'
	  WHEN ShapeCode =7 THEN  MT.PersianMeaning + CAST(NumberOfPeriod AS VARCHAR(50)) + N'واحد'
	  WHEN ShapeCode=4  THEN  MT.PersianMeaning + N'-' + MT2.PersianMeaning 
      END as Typeofuse, Price_Kharid  
	FROM dbo.IHIOPrescription WITH (NOLOCK)                                
	INNER JOIN dbo.IHIOPrescriptionDetail WITH (NOLOCK)                    
	ON IHIOPrescriptionDetail.PrescriptionId = IHIOPrescription.Id
	INNER JOIN dbo.Anbar WITH (NOLOCK)  ON Anbar.K_Code = dbo.IHIOPrescriptionDetail.GoodsCode AND Anbar.A_Code = @WarehouseCode     
	LEFT OUTER JOIN Sahmiyeh WITH (NOLOCK) ON Sahmiyeh.Sazman_Code = InsurerCode
	LEFT JOIN dbo.MedicationTerminology MT WITH (NOLOCK) ON dbo.IHIOPrescriptionDetail.ConsumptionCode =MT.Code AND MT.Type=12
    LEFT JOIN dbo.MedicationTerminology MT2 WITH (NOLOCK) ON    mt2.Code=dbo.IHIOPrescriptionDetail.ConsumptionInstructionCode AND mt2.Type=15
	LEFT OUTER JOIN dbo.MedicationTerminology MT3 WITH (NOLOCK) ON  mt3.Code=dbo.IHIOPrescriptionDetail.NumberOfPeriodcode AND mt3.Type=(16)
	LEFT OUTER JOIN dbo.MedicationTerminology MT4 WITH (NOLOCK) ON  mt4.Code=dbo.IHIOPrescriptionDetail.NumberOfPeriodcode AND mt4.Type=(17)
	WHERE  IHIOPrescriptionDetail.PrescriptionId = @DetailId
	SET @InsurerCode = (SELECT TOP 1 InsurerCode FROM @CacheData)
	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 WITH (NOLOCK) INNER JOIN dbo.Grade WITH (NOLOCK) 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, Price_Kharid
	FROM @CacheData
	ORDER BY RowIndex

    OPEN CursorIHIOPrescription
    FETCH NEXT FROM CursorIHIOPrescription INTO @GoodsCode,@DeliverCount,@CoveredCount,@InsurerPercent, @InsurerAmount, @TotalInsuredAmount, @TotalInsurerAmount, @BasePrice,
	@Description,@ConsumptionCode,@ConsumptionInstructionCode,@Typeofuse, @PurchasePrice
	SET @RowIndex = 1
    WHILE @@FETCH_STATUS = 0 
    BEGIN
	   Exec  @CtrMojodi = ChkCtrMojodi @GoodsCode , @WarehouseCode, 0  
	   IF (@TotalInsurerAmount > 0 AND (@DeliverCount* @InsurerAmount > 0 OR @CoveredCount* @BasePrice>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 @IsLock = 1
	   IF @Status IN (0,1,3)
	     SET @IsLock = 0

	   SET @InsurerPercent = CAST(CAST(@InsurerPercent as DECIMAL(9,2)) AS REAL)
	   IF @Status = 0 Set @InsurerPercent = 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,Price_Kharid)
	   VALUES(@PrescriptionId, @RowIndex, @GoodsCode, @WarehouseCode, '10000', @DeliverCountCache, @InsurerAmountCache, @CtrMojodi,1,0,0, @Differ,
	   @InsurerPercent,  @Status, @Description,@ConsumptionCode,@ConsumptionInstructionCode,@Typeofuse,@IsLock, @InsurerAmountCache + @Differ/@DeliverCountCache, @InsurerAmountCache,@InsurerAmountCache + @Differ/@DeliverCountCache, @PurchasePrice)

	   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, Price_Kharid)
	     VALUES(@PrescriptionId, @RowIndex, @GoodsCode, @WarehouseCode, '10000', @DeliverCountCache, @InsurerAmountCache, @CtrMojodi,1,0,0,  @Differ, @InsurerPercent,  @Status, @Description,@ConsumptionCode,@ConsumptionInstructionCode,@Typeofuse,@IsLock,0,0,0, @PurchasePrice)
         SET @RowIndex = @RowIndex + 1	     
	   END 
       FETCH NEXT FROM CursorIHIOPrescription INTO @GoodsCode,@DeliverCount,@CoveredCount,@InsurerPercent, @InsurerAmount, @TotalInsuredAmount, @TotalInsurerAmount, @BasePrice,@Description,@ConsumptionCode,@ConsumptionInstructionCode,@Typeofuse, @PurchasePrice
    END 
	CLOSE CursorIHIOPrescription
	DEALLOCATE CursorIHIOPrescription
	--------------------------(Insert Along)----------------------------------
	SET @DeliverCountCache = 0
	SET @DeliverCount = 0
	SET @InsurerAmountCache = 0
	SET @InsurerAmount = 0 
	SET @CtrMojodi = '0'
	DECLARE @TobedoneInsert bit 
	DECLARE @GoodsCodeSubset VARCHAR(20)
	DECLARE @QtySubset REAL 
	DECLARE @EditQty_Flag BIT   
	DECLARE @Medical CHAR(1)
	DECLARE @GoodsAlong AS TABLE
	(
	  GoodsCode VARCHAR(20),
	  Qty REAL,
	  IsAlong BIT
	)
	INSERT INTO @GoodsAlong(GoodsCode, Qty, IsAlong) 
	SELECT Kalaid.K_Code, K_Qty1, KalaId.Hamrah_Flag FROM dbo.TmpDrugHavaleh WITH (NOLOCK)
	INNER JOIN kalaid  WITH (NOLOCK) ON Kalaid.K_Code = dbo.TmpDrugHavaleh.K_Code
	WHERE Id_Havaleh = @PrescriptionId 

	DECLARE CursorIsAlong CURSOR FOR
	SELECT GoodsCode, Qty FROM @GoodsAlong
	WHERE IsAlong = 1
	OPEN CursorIsAlong
	FETCH NEXT FROM CursorIsAlong INTO @GoodsCode,@DeliverCount 
	WHILE @@FETCH_STATUS = 0
	BEGIN
	  DECLARE CursorSubset CURSOR FOR 
	  SELECT K_Code_H, K_Qty1, Price_Forosh, EditQty_Flag, 
	  ISNULL(CASE
        WHEN Darou_Flag = 1 THEN 1
        WHEN Darou_Flag IN(0,5) THEN 0
        WHEN Darou_Flag IN(6) THEN (SELECT CASE WHEN Price_Sazman.Status = 1 THEN 1 ELSE 0 END  FROM dbo.Price_Sazman WITH (NOLOCK) WHERE dbo.Anbar.K_Code = K_Code AND Sazman_Code = @InsurerCode)
	  END,0) Darou_Flag,Price_Kharid FROM dbo.Hamrah_Kala WITH (NOLOCK)
	  INNER JOIN Anbar WITH (NOLOCK) ON Anbar.K_Code = Hamrah_Kala.K_Code_H
	  INNER JOIN dbo.KalaId WITH (NOLOCK) ON KalaId.K_Code = Anbar.K_Code
	  WHERE dbo.Hamrah_Kala.K_Code = @GoodsCode AND A_Code = @WarehouseCode
	  OPEN CursorSubset
	  FETCH NEXT FROM CursorSubset INTO @GoodsCodeSubset, @QtySubset, @InsurerAmountCache,@EditQty_Flag, @Medical,@PurchasePrice
	  WHILE @@FETCH_STATUS = 0 
	  BEGIN
	    SET @TobedoneInsert = 1
		IF EXISTS(SELECT GoodsCode FROM @GoodsAlong WHERE GoodsCode = @GoodsCodeSubset)
		BEGIN
		  IF @QtySubset = 0
		    SET @TobedoneInsert = 0
		  ELSE
          BEGIN
		    SET @DeliverCountCache = CASE 
			                           WHEN @EditQty_Flag = 1 THEN (@QtySubset * @DeliverCount) - ISNULL((SELECT SUM(Qty) FROM @GoodsAlong WHERE GoodsCode = @GoodsCodeSubset),0) 
									 ELSE 
									   (@QtySubset) - ISNULL((SELECT SUM(Qty) FROM @GoodsAlong WHERE GoodsCode = @GoodsCodeSubset),0) 
									 END 
			IF @DeliverCountCache <=0 
			  SET @TobedoneInsert = 0
          END 
        END
		ELSE
		BEGIN
          IF @QtySubset = 0
		  BEGIN 
		    SET @TobedoneInsert = 1
			SET @DeliverCountCache = @QtySubset
		  END 
		  ELSE 
		  BEGIN 
		    SET @DeliverCountCache = CASE 
			                           WHEN @EditQty_Flag = 1 THEN (@QtySubset * @DeliverCount) - ISNULL((SELECT SUM(Qty) FROM @GoodsAlong WHERE GoodsCode = @GoodsCodeSubset),0) 
									 ELSE 
									   (@QtySubset) - ISNULL((SELECT SUM(Qty) FROM @GoodsAlong WHERE GoodsCode = @GoodsCodeSubset),0) 
									 END 
		    IF @DeliverCountCache <0 
			  SET @TobedoneInsert = 0
		  END 
		END  
		IF @TobedoneInsert = 1
		BEGIN 
		  IF NOT EXISTS(SELECT K_Code FROM dbo.TmpDrugHavaleh WITH (NOLOCK) WHERE Id_Havaleh =@PrescriptionId AND K_Code = @GoodsCodeSubset AND Status = @Medical)
  		  BEGIN 
			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, TypeOfMedicine, Price_Forosh_O, Main_Price,Link_Record, Price_Kharid)
			VALUES(@PrescriptionId, @RowIndex , @GoodsCodeSubset, @WarehouseCode, '10000', @DeliverCountCache, @InsurerAmountCache, 
			@CtrMojodi,1,0,0, 0,0,@Medical, 0,@InsurerAmountCache, @InsurerAmountCache,1, @PurchasePrice)
		  END 
		  ELSE
          BEGIN
		    UPDATE dbo.TmpDrugHavaleh SET K_Qty1 = K_Qty1 + @DeliverCountCache
			WHERE Id_Havaleh = @PrescriptionId AND K_Code = @GoodsCodeSubset AND Status= @Medical
          END 
          SET @RowIndex = @RowIndex + 1     
		END 
  	    FETCH NEXT FROM CursorSubset INTO @GoodsCodeSubset, @QtySubset,@InsurerAmountCache,@EditQty_Flag, @Medical, @PurchasePrice
      END 
	  CLOSE CursorSubset
	  DEALLOCATE CursorSubset
	  FETCH NEXT FROM CursorIsAlong INTO @GoodsCode,@DeliverCount
	END
	CLOSE CursorIsAlong
	DEALLOCATE CursorIsAlong
	------------------------------------------------------------------------------------------------ 
	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
	  IF CURSOR_STATUS('global','CursorAlong') = 1
	  BEGIN
		CLOSE CursorAlong
		DEALLOCATE CursorAlong
	  END
	  IF CURSOR_STATUS('global','CursorSubset') = 1
	  BEGIN
		CLOSE CursorSubset
		DEALLOCATE CursorSubset
	  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();
	  IF @@ERROR > 0 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
