CREATE PROC [dbo].[GetIRIPrescription]
    @PrescriptionId BIGINT ,
    @CalcStatus TINYINT
AS
    DECLARE @OrgPercent FLOAT ,
        @FactorId BIGINT ,
        @ContractId BIGINT ,
        @FranchisePercent FLOAT ,
        @ContractSettingId INT ,
        @FirstCoveredCon TINYINT ,
        @NonInsuredCon TINYINT ,
        @PatientAmountCon TINYINT ,
        @FranchiseCon TINYINT ,
        @SendItemsCon TINYINT ,
        @TransactionId INT ,
        @IranTechnicalFees MONEY,
		@PatientTechnicalFees MONEY,
		@SimilarDrugsDifferAmount MONEY;
------------------------------------------------------------------------------------------
SELECT  @FactorId = PrescriptionNo ,
        @ContractId = ContractId ,
        @ContractSettingId = ContractSettingId
FROM    dbo.IRI_Prescriptions
WHERE   Id = @PrescriptionId;
------------------------------------------------------------------------------------------
SELECT  @FranchisePercent = CAST(ISNULL(FranchisePercent, 0) AS TINYINT)
FROM    dbo.IRI_InsuredContracts
WHERE   Id = @ContractId;
------------------------------------------------------------------------------------------
SELECT  @FirstCoveredCon = FirstCoveredCon ,
        @NonInsuredCon = NonInsuredCon ,
        @PatientAmountCon = PatientCon ,
        @FranchiseCon = FranchiseCon ,
        @SendItemsCon = SendItemsCon,
        @TransactionId = TransactionId ,
        @IranTechnicalFees = IranTechnicalFees ,
		@PatientTechnicalFees = PatientTechnicalFees
FROM    dbo.IRI_ContractSettings
WHERE   Id = @ContractSettingId;
------------------------------------------------------------------------------------------
    IF @CalcStatus = 0
        UPDATE  FD
        SET     IsIRIObligation = 0 , 
		FD.IRIStatus = 1 		
        FROM    dbo.TmpDrugHavaleh FD
        WHERE   FD.Id_Havaleh = @FactorId;   
------------------------------------------------------------------------------------------
SELECT @SimilarDrugsDifferAmount = CAST(ISNULL(SUM(Tot_Differ)  , 0 )  AS MONEY)
FROM    dbo.TmpDrugHavaleh
WHERE   Status = 7
        AND Id_Havaleh = @FactorId;

SET @SimilarDrugsDifferAmount = ISNULL(@SimilarDrugsDifferAmount , 0)
------------------------------------------------------------------------------------------
    IF @CalcStatus IN ( 0, 2 )
        UPDATE  FH
        SET     FH.FirstCoverAmount = tblCalc.FirstCoverAmount ,
                FH.NonInsuredExpense = tblCalc.NonInsuredExpense + @SimilarDrugsDifferAmount ,
                FH.TotalAmount = tblCalc.TotalAmount + @IranTechnicalFees + @SimilarDrugsDifferAmount,
                FH.FranchiseAmount = tblCalc.FranchiseAmount , 
				FH.CoveredAmount = CASE WHEN ( tblCalc.ObligationRemainedAmount <> -1  AND tblCalc.ObligationRemainedAmount < ( tblCalc.TotalAmount + @IranTechnicalFees ) - ( tblCalc.FirstCoverAmount + tblCalc.FranchiseAmount + tblCalc.NonInsuredExpense + tblCalc.ObligationRemainedAmount ) ) THEN tblCalc.ObligationRemainedAmount				
				                        ELSE ( tblCalc.TotalAmount + @IranTechnicalFees ) - ( tblCalc.FirstCoverAmount + tblCalc.FranchiseAmount + tblCalc.NonInsuredExpense ) END ,
				FH.PatientAmount =   @SimilarDrugsDifferAmount + CASE WHEN @PatientAmountCon = 0 THEN  tblCalc.FranchiseAmount + tblCalc.NonInsuredExpense  				
				                                                      WHEN @PatientAmountCon = 1 THEN  tblCalc.FranchiseAmount + tblCalc.NonInsuredExpense + @IranTechnicalFees 
																	  ELSE tblCalc.FranchiseAmount + tblCalc.NonInsuredExpense + @PatientTechnicalFees  END ,
				FH.ExtraObligationCeiling = CASE WHEN tblCalc.ObligationRemainedAmount = -1 THEN 0 
				                                 WHEN tblCalc.ObligationRemainedAmount < ( tblCalc.TotalAmount + @IranTechnicalFees ) - ( tblCalc.FirstCoverAmount + tblCalc.FranchiseAmount + tblCalc.NonInsuredExpense  ) THEN ((tblCalc.TotalAmount + @IranTechnicalFees ) - ( tblCalc.FirstCoverAmount + tblCalc.FranchiseAmount + tblCalc.NonInsuredExpense)) - tblCalc.ObligationRemainedAmount 
												 ELSE 0 END
				FROM    TmpDrugH FH
                JOIN ( SELECT   ISNULL(SUM(CASE WHEN FD.Status NOT IN (0,1,2,3)  AND (@FirstCoveredCon = 0)  THEN ISNULL(FD.Tot_forosh * ( 100 - FD.BimarPercent )/ 100, 0) ELSE 0 END) , 0 ) FirstCoverAmount ,                                
								ISNULL(SUM(ROUND(CASE 
											 WHEN FD.Status NOT IN (0,1,2,3) AND (@FranchiseCon = 0) AND (FD.IRIStatus <> 0) THEN FD.Tot_forosh * ( FD.BimarPercent  /100)  
											 WHEN FD.Status     IN (0,3)     AND (@FranchiseCon = 3) AND (FD.IRIStatus <> 0) THEN FD.Tot_forosh * ( @FranchisePercent /100) 
											 WHEN FD.Status NOT IN (0,1,2,3) AND (@FranchiseCon = 2) AND (FD.IRIStatus <> 0) THEN FD.Tot_forosh * ((100 - FD.BimarPercent)/100) * (@FranchisePercent /100) 
									  	  ELSE 0 END  , 0 )) , 0 ) FranchiseAmount , 
								ISNULL(CAST(SUM( CASE 
								             WHEN @NonInsuredCon = 0  AND FD.IRIStatus = 0  AND Tot_Differ > 0  THEN     FD.Tot_Differ
											 WHEN @NonInsuredCon = 0  AND FD.IRIStatus = 0  AND Tot_Differ = 0  THEN     FD.Tot_forosh							 
											 WHEN @NonInsuredCon = 1 THEN 0								             
											 WHEN @NonInsuredCon = 2  AND  FD.Status     IN (0,1,2,3) THEN FD.Tot_forosh
											 WHEN @NonInsuredCon = 2  AND  FD.Status NOT IN (0,1,2,3) THEN FD.Tot_Differ  
											 WHEN @NonInsuredCon = 3  AND  FD.Status NOT IN (0,1,2,3) THEN FD.Tot_Differ
											 WHEN @NonInsuredCon = 3  AND  FD.Status     IN (1,2)     THEN FD.Tot_forosh
										  END ) AS BIGINT)  , 0 ) NonInsuredExpense ,
								ISNULL(SUM(FD.Tot_forosh + FD.Tot_Differ ) , 0 ) AS TotalAmount ,
                                FH.Id_Havaleh , 
								MIN(ObligationRemainedAmount)  ObligationRemainedAmount
                       FROM     dbo.IRI_InsuredContracts IC
                                JOIN dbo.IRI_Prescriptions PR ON PR.ContractId = IC.Id
                                JOIN dbo.TmpDrugH FH ON FH.Id_Havaleh = PR.PrescriptionNo
                                JOIN dbo.TmpDrugHavaleh FD ON FD.Id_Havaleh = FH.Id_Havaleh
							    LEFT JOIN dbo.IRI_DrugCodeLink DL ON DL.DrugCode = FD.K_Code  
                       WHERE    FH.Id_Havaleh = @FactorId
                                AND FD.Status <> 7
                                --AND IsIRIObligation = 1
                                AND IC.Id = @ContractId
                       GROUP BY PR.TransactionCode ,
                                PR.NationalCode ,
                                IC.ContractNumber ,
                                IC.InsuredPersonId ,
                                IC.ObligationCode ,
                                PR.ServiceId ,
                                IC.ContractNumber ,
                                FH.Id_Havaleh 
                     ) AS tblCalc ON tblCalc.Id_Havaleh = FH.Id_Havaleh
        WHERE   FH.Id_Havaleh = @FactorId;
------------------------------------------------------------------------------------------
   IF  ( @CalcStatus = 1 AND @SendItemsCon = 1 ) 
      SELECT 'Without Any Items';
	ELSE
	
    SELECT * , CoveredAmount = CAST(FD.TotalAmount - (FD.FirstCoverAmount + FD.Franchise + FD.NonInsuredExpense )  AS BIGINT) , 
	           
			   PatientAmount = CAST(CASE WHEN @PatientAmountCon = 0 THEN  FD.Franchise + FD.NonInsuredExpense  				
				                         WHEN @PatientAmountCon = 1 THEN  FD.Franchise + FD.NonInsuredExpense + @IranTechnicalFees 
										 ELSE FD.Franchise + FD.NonInsuredExpense + @PatientTechnicalFees  END AS BIGINT)
	FROM
    (
	SELECT  '275' TopServiceCode ,
            N'دارن' TopServiceDesc ,
            DL.IranDrugCode ServiceCode ,
            CASE WHEN ( IRIDrugs.Name IS NULL ) OR ( IRIDrugs.Name = '' ) THEN Drug.Name1 ELSE IRIDrugs.Name
            END ServiceDesc ,
            FD.Radif ,
            Drug.Name1 ,
            CAST(FD.K_Qty1 AS BIGINT) Quantity ,
            CAST(FD.K_Qty1 AS REAL) QuantityDeliver ,
            CAST(ISNULL(DU.Qty_Consum, 0) AS VARCHAR(20)) AS CycleUse ,
            CAST(ISNULL(DU.Daily, 0) AS VARCHAR(20)) AS ConsumptionFactor ,
            CAST(FD.Price_Forosh AS BIGINT) AS TarifUnit ,
            CAST(FD.K_Qty1 * FD.Price_Forosh AS BIGINT) AS Tarif ,
            NonInsuredExpense =  ISNULL(CAST(CASE 										
										WHEN @NonInsuredCon = 0  AND FD.IRIStatus = 0  AND Tot_Differ > 0  THEN     FD.Tot_Differ
										WHEN @NonInsuredCon = 0  AND FD.IRIStatus = 0  AND Tot_Differ = 0  THEN     FD.Tot_forosh
										WHEN @NonInsuredCon = 1 THEN 0
										WHEN @NonInsuredCon = 2  AND  FD.Status     IN (0,1,2,3) THEN FD.Tot_forosh  
										WHEN @NonInsuredCon = 2  AND  FD.Status NOT IN (0,1,2,3) THEN FD.Tot_Differ
										WHEN @NonInsuredCon = 3  AND  FD.Status NOT IN (0,1,2,3) THEN FD.Tot_Differ
										WHEN @NonInsuredCon = 3  AND  FD.Status     IN (1,2)     THEN FD.Tot_forosh  
								END  AS BIGINT) , 0 ),
            FirstCoverAmount = CAST(CASE WHEN FD.Status NOT IN (0,1,2,3)  AND (@FirstCoveredCon = 0)   THEN ISNULL(FD.Tot_forosh * ( 100 - FD.BimarPercent )/ 100, 0) ELSE 0 END AS BIGINT) ,
            CAST(FD.Tot_forosh + FD.Tot_Differ + @SimilarDrugsDifferAmount AS BIGINT) TotalAmount ,
            Franchise = ISNULL(CAST(ROUND(CASE 
										WHEN FD.Status NOT IN (0,1,2,3) AND (@FranchiseCon = 0) AND (FD.IRIStatus <> 0) THEN FD.Tot_forosh * (FD.BimarPercent /100)  
										WHEN FD.Status     IN (0,3)     AND (@FranchiseCon = 3) AND (FD.IRIStatus <> 0) THEN FD.Tot_forosh * (@FranchisePercent /100) 
										WHEN FD.Status NOT IN (0,1,2,3) AND (@FranchiseCon = 2) AND (FD.IRIStatus <> 0) THEN  FD.Tot_forosh * ((100 - FD.BimarPercent)/100) * (@FranchisePercent /100) 
										ELSE 0 
								   END  , 0 )  AS BIGINT) , 0 ) , 
            FD.AutoId RowId , 
			ISNULL(IRIStatus ,1) IRIStatus,
			DS.Status_Name DrugStatus,
			FD.BimarPercent PateintPercentage,  
		    IsIRIObligation = CASE WHEN DL.DrugCode  IS NOT NULL AND DL.DrugCode <> ''  AND DL.IranDrugCode  IS NOT NULL  AND DL.IranDrugCode  <> '' THEN 1 ELSE 0 END ,
			ISNULL(FD.Tot_Differ , 0 ) DifferAmount , 
			FD.Status
    FROM    dbo.TmpDrugHavaleh FD
            INNER JOIN dbo.KalaId Drug ON Drug.K_Code = FD.K_Code
            LEFT  JOIN dbo.IRI_Drugs IRIDrugs ON IRIDrugs.Code = Drug.K_Code
            LEFT  JOIN TblUMDrugHavaleh UMD ON UMD.autoid = FD.AutoId
            LEFT  JOIN dbo.TblDrugUse DU ON DU.Use_Code = UMD.use_code
			LEFT  JOIN dbo.IRI_DrugCodeLink DL ON DL.DrugCode = FD.K_Code 
			INNER JOIN dbo.Define_Status DS ON FD.Status = DS.Status_Code
    WHERE   FD.Status <> 7
            AND FD.Id_Havaleh = @FactorId
	) AS FD
    ORDER BY Radif;  
------------------------------------------------------------------------------------------
    SELECT  PR.TransactionCode ,
            PR.NationalCode ,
            Con.ContractNumber ,
            Con.InsuredPersonId ,
            Con.ObligationCode ,
            PR.ServiceId ,
            Con.ContractNumber ,
            CAST(FH.ExtraObligationCeiling AS BIGINT) ExtraObligationCeiling ,
            CAST(FH.CoveredAmount AS BIGINT) CoveredAmount ,
			CAST(FH.FirstCoverAmount AS BIGINT) FirstCoverAmount ,
            CAST(FH.FranchiseAmount AS BIGINT) FranchiseAmount ,
            CAST(FH.NonInsuredExpense AS BIGINT) NonInsuredExpense ,
            CAST(FH.TotalAmount AS BIGINT) TotalAmount ,
			CAST(FH.PatientAmount  AS BIGINT) PatientAmount ,
            @IranTechnicalFees TechnicalFees
    FROM    dbo.IRI_InsuredContracts Con
            JOIN dbo.IRI_Prescriptions PR ON PR.ContractId = Con.Id
            JOIN dbo.TmpDrugH FH ON FH.Id_Havaleh = PR.PrescriptionNo
            JOIN ( SELECT   Id_Havaleh
                   FROM     dbo.TmpDrugHavaleh
                   WHERE    Status <> 7
                            --AND IsIRIObligation = 1
                   GROUP BY Id_Havaleh
                 ) tmpFactorDetail ON tmpFactorDetail.Id_Havaleh = FH.Id_Havaleh
    WHERE   FH.Id_Havaleh = @FactorId
            AND Con.Id = @ContractId;
