CREATE PROC [dbo].[SupportiveOrganizationReport]
    @ReportType TINYINT,
    @InsuranceCode VARCHAR(3),
    @IsRegisterDate BIT,
    @TransactionId BIGINT,
    @StartDateInput VARCHAR(10),
    @EndDateInput VARCHAR(10),
    @StartTime VARCHAR(8),
    @EndTime VARCHAR(8),
    @ComputerName VARCHAR(MAX)
AS
------------------>    Calc Date Time Values
DECLARE @StartDate DATETIME = CASE
                                  WHEN @IsRegisterDate = 1 THEN
                                      dbo.ShamsiToMiladi(@StartDateInput) + ' ' + @StartTime
                                  ELSE
                                      CAST(dbo.ShamsiToMiladi(@StartDateInput) AS DATE)
                              END;
DECLARE @EndDate DATETIME = CASE
                                WHEN @IsRegisterDate = 1 THEN
                                    dbo.ShamsiToMiladi(@EndDateInput) + ' ' + @EndTime
                                ELSE
                                    CAST(dbo.ShamsiToMiladi(@EndDateInput) AS DATE)
                            END;
-----------------
IF @ReportType = 0
BEGIN
    ------------------>    Calc Minimal Report
    SELECT hd.Nezam_No DoctorMediaclCode,
           CAST(0 AS MONEY) TotalPrice,
           ISNULL(hd.TotPriceDiffer, 0) AS TotalDiffer,
           ROUND(ov.Price, 0) TotalTransactionPrice,
           ISNULL(hd.TotPriceAzad, 0) TotalNonInsured,
           RIGHT(dbo.MiladiToShamsi(hd.Tarikh_Noskheh), 8) AS VisitDate,
           RIGHT(dbo.MiladiToShamsi(hd.Tarikh), 8) AS RegisterDate,
           o.Name AS TrasacrtionName,
           ov.AutoId TransactionId,
           ov.Code_Over_Under TransactionCode,
           [Percent] TransactionPercent,
           ISNULL(ov.Over_Row_No, 0) TransactionRowNumber,
           hd.Note_Code InsuranceBookletNumber,
           hd.OverFlagBimeh IsInsuranceConfirm,
           hd.User_id UserCode,
           hd.TotPriceFaniBimar TotalPatientTechnicalFees,
           hd.TotPriceBimar TotalPatientPrice,
           hd.TotPriceSazman TotalInsurancePrice,
           hd.TotPriceBimar + hd.TotPriceSazman TotalInsuranceDrugPrice,
           hd.TotPriceforosh TotalPayPaitient,
           hd.Sazman_Code InsuranceCode,
           hd.Nu_Forosh FactorNumber,
           hd.Id_Havaleh HeaderId,
           s.Sa_Name AS InsuranceName,
           PrescriptionKind = CASE
                                  WHEN hd.kind_Save = 4 THEN
                                      1
                                  WHEN ISNULL(ov.Over_Del_Flag, 1) = 2 THEN
                                      3
                                  WHEN ISNULL(ov.Over_Del_Flag, 1) = 0 THEN
                                      2
                                  ELSE
                                      4
                              END,
           ISNULL(ov.Over_Del_Flag, 1) TransactionFlag,
           hd.Resive_Name PatientName
    FROM Over_Under_Factor_Drug ov WITH (NOLOCK)
        INNER JOIN dbo.DrugH hd WITH (NOLOCK)
            ON ov.Id_Havaleh = hd.Id_Havaleh
        INNER JOIN Over_Under o WITH (NOLOCK)
            ON ov.Code_Over_Under = o.AutoId
        INNER JOIN Sahmiyeh s
            ON s.Sazman_Code = hd.Sazman_Code
    WHERE hd.Kind_Flag = 1
          AND Action_Code NOT IN ( -4, -5, -6 )
          AND
          (
              (
                  @IsRegisterDate = 1
                  AND hd.Tarikh
          BETWEEN @StartDate AND @EndDate
              )
              OR
              (
                  @IsRegisterDate = 0
                  AND hd.Tarikh_Noskheh
          BETWEEN @StartDate AND @EndDate
              )
          )
          AND (hd.Sazman_Code = CASE
                                    WHEN ISNULL(@InsuranceCode, '0') = '0' THEN
                                        hd.Sazman_Code
                                    ELSE
                                        @InsuranceCode
                                END
              )
          AND (o.AutoId = CASE
                              WHEN ISNULL(@TransactionId, 0) <> 0 THEN
                                  @TransactionId
                              ELSE
                                  o.AutoId
                          END
              )
    ORDER BY CASE
                 WHEN @IsRegisterDate = 1 THEN
                     hd.Tarikh
                 ELSE
                     hd.Tarikh_Noskheh
             END,
             hd.Id_Havaleh;
    ------------------>    Calc Summary Of Minimal Report 
    SELECT ROUND(SUM(ov.Price), 0) AS TotalDiscount,
           COUNT(hd.Id_Havaleh) AS [RowCount],
           SUM(ISNULL(TotPriceDiffer, 0)) TotalDiffer,
           SUM(TotPriceBimar) TotalPatientAmount,
           --SUM(dt.TotalPrice + ISNULL(TotPriceDiffer, 0)) AS TotalInvoiceAmount,
           CAST(0 AS MONEY) TotalInvoiceAmount,
           SUM(TotPriceBimar + TotPriceSazman) TotalInsuranceAmount,
           SUM(ISNULL(hd.TotPriceAzad, 0)) AS TotalNonInsurance,
           SUM(TotPriceFaniBimar) AS TotalTechnicalFees,
           SUM(TotPriceforosh) AS TotalSalesPrice
    FROM Over_Under_Factor_Drug ov WITH (NOLOCK)
        INNER JOIN DrugH hd WITH (NOLOCK)
            ON ov.Id_Havaleh = hd.Id_Havaleh
        INNER JOIN Over_Under WITH (NOLOCK)
            ON ov.Code_Over_Under = Over_Under.AutoId
    WHERE hd.Kind_Flag = 1
          AND Action_Code NOT IN ( -4, -5, -6 )
          AND
          (
              (
                  @IsRegisterDate = 1
                  AND hd.Tarikh
          BETWEEN @StartDate AND @EndDate
              )
              OR
              (
                  @IsRegisterDate = 0
                  AND hd.Tarikh_Noskheh
          BETWEEN @StartDate AND @EndDate
              )
          )
          AND (hd.Sazman_Code = CASE
                                    WHEN ISNULL(@InsuranceCode, '0') = '0' THEN
                                        hd.Sazman_Code
                                    ELSE
                                        @InsuranceCode
                                END
              )
          AND (Over_Under.AutoId = CASE
                                       WHEN ISNULL(@TransactionId, 0) <> 0 THEN
                                           @TransactionId
                                       ELSE
                                           Over_Under.AutoId
                                   END
              )
          AND ISNULL(ov.Over_Del_Flag, 1) = 1;
END;
ELSE
BEGIN
    ------------------>   Create Temp Table
    IF NOT EXISTS (SELECT name FROM tempdb.dbo.sysobjects WHERE name = '##dt')
    BEGIN
        CREATE TABLE ##dt
        (
            Id NUMERIC PRIMARY KEY,
            detailCount INT,
            TotalPrice MONEY
                DEFAULT (0),
            ComputerName VARCHAR(MAX)
        ) ON [PRIMARY];
    END;
    ELSE
        DELETE FROM ##dt;
    ------------------>  Calc And Insert Prescription's Detail 
    INSERT INTO ##dt
    SELECT dt.Id_Havaleh DetailId,
           COUNT(AutoId) detailCount,
           SUM(Tot_forosh) TotalPrice,
           @ComputerName
    FROM dbo.DrugHavaleh dt WITH (NOLOCK)
        JOIN dbo.DrugH hd WITH (NOLOCK)
            ON hd.Id_Havaleh = dt.Id_Havaleh
    WHERE Status <> 8
          AND
          (
              (
                  @IsRegisterDate = 1
                  AND hd.Tarikh
          BETWEEN @StartDate AND @EndDate
              )
              OR
              (
                  @IsRegisterDate = 0
                  AND hd.Tarikh_Noskheh
          BETWEEN @StartDate AND @EndDate
              )
          )
          AND (hd.Sazman_Code = CASE
                                    WHEN ISNULL(@InsuranceCode, '0') = '0' THEN
                                        hd.Sazman_Code
                                    ELSE
                                        @InsuranceCode
                                END
              )
          AND hd.Kind_Flag = 1
    GROUP BY dt.Id_Havaleh;
    IF @ReportType = 1
    BEGIN
        ------------------>    Calc Normal Report
        SELECT hd.Nezam_No DoctorMediaclCode,
               doc.Family + ' ' + doc.Name DoctorName,
               dt.TotalPrice + ISNULL(hd.TotPriceDiffer, 0) TotalPrice,
               ISNULL(hd.TotPriceDiffer, 0) AS TotalDiffer,
               ROUND(ov.Price, 0) TotalTransactionPrice,
               ISNULL(hd.TotPriceAzad, 0) TotalNonInsured,
               RIGHT(dbo.MiladiToShamsi(hd.Tarikh_Paziresh), 8) AS ReceptionDate,
               RIGHT(dbo.MiladiToShamsi(hd.Tarikh_Etebar), 8) AS ValidatyDate,
               RIGHT(dbo.MiladiToShamsi(hd.Tarikh_Noskheh), 8) AS VisitDate,
               RIGHT(dbo.MiladiToShamsi(hd.Tarikh), 8) AS RegisterDate,
               CONVERT(VARCHAR(10), hd.Tarikh, 108) AS RegisterTime,
               o.Name AS TrasacrtionName,
               ov.AutoId TransactionId,
               ov.Code_Over_Under TransactionCode,
               [Percent] TransactionPercent,
               ISNULL(ov.Over_Row_No, 0) TransactionRowNumber,
               hd.Note_Code InsuranceBookletNumber,
               hd.OverFlagBimeh IsInsuranceConfirm,
               hd.User_id UserCode,
               hd.TotPriceFaniBimar TotalPatientTechnicalFees,
               hd.TotPriceFaniSazman TotalInsuranceTechnicalFees,
               hd.TotPriceBimar TotalPatientPrice,
               hd.TotPriceSazman TotalInsurancePrice,
               hd.TotPriceBimar + hd.TotPriceSazman TotalInsuranceDrugPrice,
               hd.Page_No InsuranceBookletPaperNumber,
               hd.TotPriceforosh TotalPayPaitient,
               hd.Sazman_Code InsuranceCode,
               hd.Nu_Forosh FactorNumber,
               hd.Dumy_Code DummyCode,
               hd.Id_Havaleh HeaderId,
               s.Sa_Name AS InsuranceName,
               s.Bimar_Percent PatientPercent,
               nt.Agreement_No InsuranceAgreemntNumber,
               nt.Name PatientFirstName,
               nt.Family PatientLastName,
               hd.Resive_Name PatientName,
               nt.Meli_Card_No PatientNationalCode,
               nt.VeteransPercent,
               nt.TypeSex Gender,
               nt.Mobile,
               nt.Tel,
               nt.Mess Msg,
               dt.detailCount DrugItemCount,
               PrescriptionKind = CASE
                                      WHEN hd.kind_Save = 4 THEN
                                          1
                                      WHEN ISNULL(ov.Over_Del_Flag, 1) = 2 THEN
                                          3
                                      WHEN ISNULL(ov.Over_Del_Flag, 1) = 0 THEN
                                          2
                                      ELSE
                                          4
                                  END,
               ISNULL(ov.Over_Del_Flag, 1) TransactionFlag,
               il.ill_Name IllName
        FROM Over_Under_Factor_Drug ov WITH (NOLOCK)
            INNER JOIN dbo.DrugH hd WITH (NOLOCK)
                ON ov.Id_Havaleh = hd.Id_Havaleh
            LEFT OUTER JOIN Doctor doc
                ON hd.Nezam_No = doc.Nezam_No
            INNER JOIN Over_Under o WITH (NOLOCK)
                ON ov.Code_Over_Under = o.AutoId
            INNER JOIN Sahmiyeh s
                ON s.Sazman_Code = hd.Sazman_Code
            LEFT OUTER JOIN Notebimeh nt
                ON (
                       nt.Note_Code = hd.Note_Code
                       AND hd.Sazman_Code = nt.Sazman_Code
                   )
            INNER JOIN ##dt AS dt
                ON dt.Id = hd.Id_Havaleh
            LEFT JOIN dbo.tbl_Ill il
                ON il.ill_Code = nt.Agreement_No
        WHERE hd.Kind_Flag = 1
              AND Action_Code NOT IN ( -4, -5, -6 )
              AND
              (
                  (
                      @IsRegisterDate = 1
                      AND hd.Tarikh
              BETWEEN @StartDate AND @EndDate
                  )
                  OR
                  (
                      @IsRegisterDate = 0
                      AND hd.Tarikh_Noskheh
              BETWEEN @StartDate AND @EndDate
                  )
              )
              AND (hd.Sazman_Code = CASE
                                        WHEN ISNULL(@InsuranceCode, '0') = '0' THEN
                                            hd.Sazman_Code
                                        ELSE
                                            @InsuranceCode
                                    END
                  )
              AND (o.AutoId = CASE
                                  WHEN ISNULL(@TransactionId, 0) <> 0 THEN
                                      @TransactionId
                                  ELSE
                                      o.AutoId
                              END
                  )
        ORDER BY CASE
                     WHEN @IsRegisterDate = 1 THEN
                         hd.Tarikh
                     ELSE
                         hd.Tarikh_Noskheh
                 END,
                 hd.Id_Havaleh;

        ------------------>    Calc Summary Of Normal Report 
        SELECT ROUND(SUM(ov.Price), 0) AS TotalDiscount,
               COUNT(hd.Id_Havaleh) AS [RowCount],
               SUM(ISNULL(TotPriceDiffer, 0)) TotalDiffer,
               SUM(TotPriceBimar) TotalPatientAmount,
               SUM(dt.TotalPrice + ISNULL(TotPriceDiffer, 0)) AS TotalInvoiceAmount,
               SUM(TotPriceBimar + TotPriceSazman) TotalInsuranceAmount,
               SUM(ISNULL(hd.TotPriceAzad, 0)) AS TotalNonInsurance,
               SUM(TotPriceFaniBimar) AS TotalTechnicalFees,
               SUM(TotPriceforosh) AS TotalSalesPrice
        FROM Over_Under_Factor_Drug ov WITH (NOLOCK)
            INNER JOIN DrugH hd WITH (NOLOCK)
                ON ov.Id_Havaleh = hd.Id_Havaleh
            INNER JOIN Over_Under WITH (NOLOCK)
                ON ov.Code_Over_Under = Over_Under.AutoId
            --INNER JOIN Sahmiyeh
            --    ON Sahmiyeh.Sazman_Code = hd.Sazman_Code
            INNER JOIN ##dt AS dt
                ON dt.Id = hd.Id_Havaleh
        WHERE hd.Kind_Flag = 1
              AND Action_Code NOT IN ( -4, -5, -6 )
              AND
              (
                  (
                      @IsRegisterDate = 1
                      AND hd.Tarikh
              BETWEEN @StartDate AND @EndDate
                  )
                  OR
                  (
                      @IsRegisterDate = 0
                      AND hd.Tarikh_Noskheh
              BETWEEN @StartDate AND @EndDate
                  )
              )
              AND (hd.Sazman_Code = CASE
                                        WHEN ISNULL(@InsuranceCode, '0') = '0' THEN
                                            hd.Sazman_Code
                                        ELSE
                                            @InsuranceCode
                                    END
                  )
              AND (Over_Under.AutoId = CASE
                                           WHEN ISNULL(@TransactionId, 0) <> 0 THEN
                                               @TransactionId
                                           ELSE
                                               Over_Under.AutoId
                                       END
                  )
              AND ISNULL(ov.Over_Del_Flag, 1) = 1;
    END;

    IF @ReportType = 2
        ------------------>    Calc Summary Report
        SELECT SUM(dt.TotalPrice + ISNULL(hd.TotPriceDiffer, 0)) TotalPrice,
               SUM(ROUND(ov.Price, 0)) TotalTransactionPrice,
               SUM(hd.TotPriceFaniBimar) TotalPatientTechnicalFees,
               SUM(hd.TotPriceFaniSazman) TotalInsuranceTechnicalFees,
               SUM(hd.TotPriceBimar) TotalPatientPrice,
               SUM(hd.TotPriceSazman) TotalinsurancePrice,
               SUM(TotPriceBimar + TotPriceSazman) TotalInsuranceDrugPrice,
               MIN(o.Acc_Code) AccountCode,
               SUM(hd.TotPriceforosh) TotalPayPaitient,
               SUM(ISNULL(hd.TotPriceAzad, 0)) TotalNonInsured,
               o.Name TrasacrtionName,
               COUNT(ISNULL(hd.Id_Havaleh, 0)) FactorCount
        FROM Over_Under_Factor_Drug ov
            INNER JOIN DrugH hd WITH (NOLOCK)
                ON ov.Id_Havaleh = hd.Id_Havaleh
            LEFT OUTER JOIN Doctor doc WITH (NOLOCK)
                ON hd.Nezam_No = doc.Nezam_No
            INNER JOIN Over_Under o WITH (NOLOCK)
                ON ov.Code_Over_Under = o.AutoId
            INNER JOIN Sahmiyeh
                ON Sahmiyeh.Sazman_Code = hd.Sazman_Code
            LEFT OUTER JOIN Notebimeh WITH (NOLOCK)
                ON (
                       Notebimeh.Note_Code = hd.Note_Code
                       AND hd.Sazman_Code = Notebimeh.Sazman_Code
                   )
            INNER JOIN ##dt AS dt
                ON dt.Id = hd.Id_Havaleh
        WHERE hd.Kind_Flag = 1
              AND Action_Code NOT IN ( -4, -5, -6 )
              AND ISNULL(ov.Over_Del_Flag, 1) = 1
              AND
              (
                  (
                      @IsRegisterDate = 1
                      AND hd.Tarikh
              BETWEEN @StartDate AND @EndDate
                  )
                  OR
                  (
                      @IsRegisterDate = 0
                      AND hd.Tarikh_Noskheh
              BETWEEN @StartDate AND @EndDate
                  )
              )
        GROUP BY o.Name
        ORDER BY o.Name;
END;