CREATE PROCEDURE [dbo].[AddTaminPrescription] @PrescriptionId BIGINT , @WarehouseCode VARCHAR(3), @TrackingCode INT 
AS
	DECLARE @CtrMojodi CHAR(1)
	DECLARE @MedicalGradeCode VARCHAR(3)
	DECLARE @MedicalGradeName NVARCHAR(45)
	DECLARE @MedicalGradeStatus NVARCHAR(10)
	Declare @Id UNIQUEIDENTIFIER
	DECLARE @InsurerCode VARCHAR(5)
	DECLARE @RowIndex INT 
	DECLARE @GoodsCode VARCHAR(15)
	DECLARE @DeliverCount INT 
	DECLARE @DeliverCountCache INT 
	DECLARE @Differ MONEY
	DECLARE @Status TINYINT
	DECLARE @InsurerPercent REAL 
	DECLARE @SalePrice MONEY
	DECLARE @DrugPrice money  
	DECLARE @Instruction nvarchar(MAX) 
	DECLARE @IsLock TINYINT
	DECLARE @PurchasePrice MONEY

	DECLARE @CacheData AS TABLE
	(
	Id UNIQUEIDENTIFIER,
	InsurerCode VARCHAR(3),
	InsurerName NVARCHAR(50),
	InsuredName NVARCHAR(50),
	InsuredLastName NVARCHAR(50),
	BirthDate VARCHAR(10), 
	Mobile VARCHAR(15),
	NationalCode VARCHAR(12),
	Medicalid INT ,
	DoctorName NVARCHAR(100),
	DoctorLastName NVARCHAR(100),
	MedicalGradeCode VARCHAR(3),
	MedicalGradeName NVARCHAR(30),
	MedicalGradeStatus NVARCHAR(10),
	AccountValidTo VARCHAR(10),
	VisitDate VARCHAR(10),
	Description VARCHAR(10)
	)
	BEGIN TRY
	BEGIN TRAN 
    Select Top 1 @Id= Id , @InsurerCode = PrescriptionTypeCode FROM dbo.TaminPrescription Where TrackingCode = @TrackingCode ORDER BY CreatedOn Desc
	SET @InsurerCode = (SELECT value FROM dbo.GeneralSetting WHERE RIGHT(Parameter,4) = @InsurerCode)
	DELETE FROM @CacheData
	INSERT INTO @CacheData(Id,InsurerCode,InsurerName,InsuredName,InsuredLastName,  BirthDate, Mobile,NationalCode,Medicalid, DoctorName, DoctorLastName,
						   AccountValidTo, VisitDate, Description)
    SELECT TaminPrescription.Id, CASE WHEN ISNULL(@InsurerCode,'') = '' THEN InsurerCode ELSE @InsurerCode END InsurerCode, Sa_Name InsurerName,InsuredName, InsuredLastName,
	Dbo.MiladiToShamsi(BirthDate) BirthDate, Mobile, NationalCode,
	Medicalid, DoctorName, DoctorLastName, Dbo.MiladiToShamsi(GETDATE()) AccountValidTo , 
	Dbo.MiladiToShamsi(PrescriptionDate) VisitDate,'Success' Description
	FROM dbo.TaminPrescription WITH (NOLOCK)                                
	LEFT OUTER JOIN dbo.Sahmiyeh WITH (NOLOCK) ON Sahmiyeh.Sazman_Code = (CASE WHEN ISNULL(@InsurerCode,'') = '' THEN InsurerCode ELSE @InsurerCode END )
	WHERE  TaminPrescription.Id = @Id
	
	IF (SELECT COUNT(0) FROM @CacheData) = 0   
	BEGIN
	  ROLLBACK TRAN
	  SELECT  NEWID() Id, '' InsurerCode, '' InsurerName,'' InsuredName, '' InsuredLastName, '' 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 CursorTaminPrescription CURSOR FOR
	SELECT RowIndex, GoodsCode, DeliverCount, (InsurerAmount + InsuredAmount)/DeliverCount SalePrice, DifferenceAmount, 
	CASE
      WHEN Franshiz = 0 THEN 0 
	  ELSE 
	  100-Franshiz
	END AS Franshiz , 
	CASE
      WHEN Franshiz = 100 THEN 4
      WHEN Franshiz = 0 THEN 0
      WHEN DifferenceAmount > 0  THEN 6
      WHEN DifferenceAmount = 0 AND Franshiz > 0  THEN 5
	END AS Status, InsurerCeilingAmount, Instruction, Price_Kharid
	FROM dbo.TaminPrescriptionDetail WITH (NOLOCK)
	INNER JOIN dbo.Anbar WITH (NOLOCK)  ON Anbar.K_Code = dbo.TaminPrescriptionDetail.GoodsCode AND Anbar.A_Code = @WarehouseCode     
    WHERE PrescriptionId = @Id
	ORDER BY RowIndex

    OPEN CursorTaminPrescription
    FETCH NEXT FROM CursorTaminPrescription INTO @RowIndex, @GoodsCode, @DeliverCount, @SalePrice, @Differ,@InsurerPercent,	@Status,@DrugPrice, @Instruction, @PurchasePrice
    WHILE @@FETCH_STATUS = 0 
    BEGIN
       Exec  @CtrMojodi = ChkCtrMojodi @GoodsCode , @WarehouseCode, 0  
	   SET @IsLock = 1
	   IF @Status IN (0,1,3)
	     SET @IsLock = 0
		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, TypeOfMedicine, Price_Forosh_O, Price_Sazman_O, Main_Price, Use_Name, Price_Kharid)
	   VALUES(@PrescriptionId, @RowIndex, @GoodsCode, @WarehouseCode,'10000', @DeliverCount, @SalePrice, @CtrMojodi, 1,0,0,@Differ,@InsurerPercent,
	   @Status, @IsLock,@DrugPrice, @DrugPrice, @DrugPrice, @Instruction,@PurchasePrice)

      FETCH NEXT FROM CursorTaminPrescription INTO @RowIndex, @GoodsCode, @DeliverCount, @SalePrice, @Differ,@InsurerPercent,@Status,@DrugPrice, @Instruction,@PurchasePrice
    END 
	CLOSE CursorTaminPrescription
	DEALLOCATE CursorTaminPrescription
	--------------------------(Insert Along)----------------------------------
	SET @DeliverCountCache = 0
	SET @DeliverCount = 0
	SET @SalePrice = 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
	INNER JOIN kalaid 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, dbo.Hamrah_Kala.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
	  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, @SalePrice, @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 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 + 1 , @GoodsCodeSubset, @WarehouseCode, '10000', @DeliverCountCache, @SalePrice, 
			@CtrMojodi,1,0,0, 0,0,@Medical, 0,@SalePrice, @SalePrice,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,@SalePrice, @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,BirthDate, Mobile, 
	NationalCode,Medicalid, DoctorName, DoctorLastName, MedicalGradeCode, MedicalGradeName, MedicalGradeStatus,  AccountValidTo , VisitDate, 'Success' Description FROM @CacheData
	END TRY
	BEGIN CATCH
	  IF CURSOR_STATUS('global','CursorTaminPrescription') = 1
	  BEGIN
		CLOSE CursorTaminPrescription
		DEALLOCATE CursorTaminPrescription
	  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,'' BirthDate, '' Mobile, 
	          '' NationalCode,0 Medicalid, '' DoctorName, '' DoctorLastName, '' ,'' MedicalGradeCode, '' MedicalGradeName, '' MedicalGradeStatus,  '' AccountValidTo , '' VisitDate, '-2' Description 
	END CATCH