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 @Differ MONEY
	DECLARE @Status TINYINT
	DECLARE @InsurerPercent REAL 
	DECLARE @SalePrice MONEY
	DECLARE @DrugPrice money  
	DECLARE @Instruction nvarchar(MAX) 

	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
	FROM dbo.TaminPrescriptionDetail WITH (NOLOCK) WHERE PrescriptionId = @Id
	ORDER BY RowIndex

    OPEN CursorTaminPrescription
    FETCH NEXT FROM CursorTaminPrescription INTO @RowIndex, @GoodsCode, @DeliverCount, @SalePrice, @Differ,@InsurerPercent,	@Status,@DrugPrice, @Instruction
    WHILE @@FETCH_STATUS = 0 
    BEGIN
       Exec  @CtrMojodi = ChkCtrMojodi @GoodsCode , @WarehouseCode, 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)
	   VALUES(@PrescriptionId, @RowIndex, @GoodsCode, @WarehouseCode,'10000', @DeliverCount, @SalePrice, @CtrMojodi, 1,0,0,@Differ,@InsurerPercent,
	   @Status, 1,@DrugPrice, @DrugPrice, @DrugPrice, @Instruction)

      FETCH NEXT FROM CursorTaminPrescription INTO @RowIndex, @GoodsCode, @DeliverCount, @SalePrice, @Differ,@InsurerPercent,@Status,@DrugPrice, @Instruction
    END 
	CLOSE CursorTaminPrescription
	DEALLOCATE CursorTaminPrescription
	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
