CREATE PROCEDURE [dbo].[SupplementaryInsurance_CalcFormula] @FactorId BIGINT , @TotPrice MONEY , @TransactionId SMALLINT, 
                @InsuranceCode VARCHAR(3), @PatientTechnicalRight Money,  @Formula NVARCHAR(MAX)
AS 

DECLARE @DifferAmount MONEY
DECLARE @PatientAmount MONEY


DECLARE @GoodsAmount MONEY
DECLARE @NonInsuranceAmount MONEY
DECLARE @EquipmentAmount MONEY
DECLARE @IncDecValue MONEY
DECLARE @CalcTechnicalRight SMALLINT = (SELECT [PERCENT] FROM dbo.SupplementaryInsurance WHERE Code = @InsuranceCode)*@PatientTechnicalRight/100
UPDATE dbo.TmpDrugHavaleh SET SupplementaryInsurancePrice = 0 WHERE Id_Havaleh = @FactorId

DECLARE @SupplementaryInsurance AS TABLE
(
  Autoid BIGINT,
  InsuranceCode VARCHAR(3),
  GoodsCode VARCHAR(15),
  DifferAmount MONEY , 
  PatientAmount MONEY , 
  GoodsAmount MONEY , 
  NonInsuranceAmount MONEY , 
  EquipmentAmount MONEY ,
  TotPrice MONEY,
  Status TINYINT
)

INSERT INTO @SupplementaryInsurance
SELECT Autoid, InsuranceCode,GoodsCode, DifferAmount, PatientAmount, GoodsAmount, NonInsuranceAmount, EquipmentAmount,TotPrice, Status FROM (
SELECT Autoid,InsuranceCode,

K_Code GoodsCode, 

CASE
  WHEN (ISNULL(DifferAmount,0) <> 1) AND @InsuranceCode <> ''  THEN (CASE
																WHEN (ISNULL(DifferAmount,0) <= Tot_Differ/K_Qty1) THEN DifferAmount * K_Qty1
																ELSE Tot_Differ
															  END 
															 )
  WHEN ISNULL(DifferAmount,0) = 1 THEN Tot_Differ
  ELSE 0
END AS DifferAmount,


CASE
  WHEN ISNULL(PatientAmount,0) <> 1 AND @InsuranceCode <> '' THEN (CASE
															  WHEN (ISNULL(PatientAmount,0) <= (Price_Forosh * BimarPercent/100)/K_Qty1) THEN ISNULL(PatientAmount,0) * K_Qty1
														      ELSE Price_Forosh * BimarPercent/100
															END 
														   )

  WHEN ISNULL(PatientAmount,0) = 1 THEN Tot_forosh * BimarPercent/100  
  ELSE 0 
END AS PatientAmount,

CASE
  WHEN Status = 1 THEN  (CASE
                           WHEN ISNULL(GoodsAmount,0) <> 1 AND @InsuranceCode <> '' THEN  (CASE
																					  WHEN ISNULL(GoodsAmount,0) <= Price_Forosh THEN ISNULL(GoodsAmount,0) * K_Qty1	
																					  ELSE 0
																					END  
																					)
						   WHEN ISNULL(GoodsAmount,0) = 1 THEN Tot_forosh
						   ELSE 0
                         END 
						)
 ELSE 0                        
END AS GoodsAmount,


CASE
  WHEN Status = 0 THEN  (CASE
                           WHEN ISNULL(NonInsuranceAmount,0) <> 1 AND @InsuranceCode <> '' THEN  (CASE
																							  WHEN ISNULL(NonInsuranceAmount,0) <= Price_Forosh THEN ISNULL(NonInsuranceAmount,0) * K_Qty1	
																							  ELSE 0
																							END  
																						   )
						   WHEN ISNULL(NonInsuranceAmount,0) = 1 THEN Tot_forosh
						   ELSE 0
                         END 
						)
 ELSE 0                        
END AS NonInsuranceAmount,
CASE
 WHEN Status = 2  THEN  Tot_forosh 
 ELSE 0 
END AS EquipmentAmount, 0 TotPrice, Status
FROM dbo.TmpDrugHavaleh
INNER JOIN  dbo.SupplementaryInsurance_DrugCondition ON GoodsCode = K_Code
WHERE Id_Havaleh  =@FactorId
AND Status <> 7
) AS SupplementaryInsurance
Where InsuranceCode =@InsuranceCode


INSERT INTO @SupplementaryInsurance
SELECT Autoid, '', K_Code,
/*CASE 
 WHEN Tot_Differ <> 0 THEN Tot_Differ ELSE 0
END AS DifferAmount,*/
0 AS DifferAmount,
CASE
  WHEN Status = 7 THEN 0
  ELSE Tot_forosh * BimarPercent/100 
END AS PatientAmount,
/*CASE
  WHEN Status = 1 THEN Tot_forosh 
  ELSE 0 
END AS GoodsAmount,*/
0 AS GoodsAmount, 
/*CASE
  WHEN Status = 0 THEN Tot_forosh 
  ELSE 0 
END AS NonInsuranceAmount, */
0 AS NonInsuranceAmount,
CASE
  WHEN Status = 2 THEN Tot_forosh 
  ELSE 0 
END AS EquipmentAmount,0, Status
FROM dbo.TmpDrugHavaleh 
WHERE Autoid NOT IN(SELECT Autoid FROM @SupplementaryInsurance)
AND Id_Havaleh = @FactorId

---------------------------------------------------(SupplementaryInsurance)------------------------------------------------------
UPDATE @SupplementaryInsurance SET TotPrice = SumValue FROM @SupplementaryInsurance
INNER JOIN 
(
SELECT Status, GoodsCode, REPLACE(SUBSTRING(@Formula, 0, CHARINDEX('A', @Formula) - 1), '0.', '')*NonInsuranceAmount/100 +
		REPLACE( SUBSTRING(
								@Formula,
								CHARINDEX('A', @Formula) + 2,
								CHARINDEX('B', @Formula) - CHARINDEX('A', @Formula) - 3
							),
					'0.',
					''
				)*GoodsAmount/100 +
		REPLACE(
					SUBSTRING(
								@Formula,
								CHARINDEX('B', @Formula) + 2,
								CHARINDEX('C', @Formula) - CHARINDEX('B', @Formula) - 3
							),
					'0.',
					''
				)*DifferAmount/100 +
		REPLACE(
					SUBSTRING(
								@Formula,
								CHARINDEX('C', @Formula) + 2,
								CHARINDEX('D', @Formula) - CHARINDEX('C', @Formula) - 3
							),
					'0.',
					''
				)*PatientAmount/100 +
		REPLACE(
					SUBSTRING(
								@Formula,
								CHARINDEX('D', @Formula) + 2,
								CHARINDEX('E', @Formula) - CHARINDEX('D', @Formula) - 3
							),
					'0.',
					''
				) *EquipmentAmount/100 SumValue FROM @SupplementaryInsurance
) AS T ON T.GoodsCode = [@SupplementaryInsurance].GoodsCode
AND [@SupplementaryInsurance].status = T.Status


--------------------------------(Update Price In TmpDrugHavaleh And Over_Under_Factor_Drug )-----------------------------
UPDATE dbo.TmpDrugHavaleh SET SupplementaryInsurancePrice = [@SupplementaryInsurance].TotPrice FROM dbo.TmpDrugHavaleh
INNER JOIN @SupplementaryInsurance ON GoodsCode = K_Code
AND [@SupplementaryInsurance].Status = TmpDrugHavaleh.status
WHERE Id_Havaleh = @FactorId

SELECT @IncDecValue = (SELECT SUM(TotPrice) FROM @SupplementaryInsurance) + @CalcTechnicalRight
UPDATE Tmp_Over_Under_Factor_Drug Set Price = @IncDecValue,[Percent] = @IncDecValue * 100 /@TotPrice
WHERE Id_Havaleh = @FactorId AND Code_Over_Under = @TransactionId
