CREATE PROC [dbo].[AddTTACPrescription]
    @PharmacyId DECIMAL(19, 0),
    @WarehouseCode VARCHAR(3),
    @TrackingCode INT
AS
BEGIN

    DECLARE @Id UNIQUEIDENTIFIER;
    DECLARE @WarehouseName NVARCHAR(30);
    DECLARE @StatusMsg NVARCHAR(MAX);
    DECLARE @StatusCode TINYINT = 0;

    DECLARE @Header AS TABLE
    (
        Id UNIQUEIDENTIFIER NOT NULL PRIMARY KEY,
        InsuranceCode VARCHAR(3) NOT NULL,
        InsuranceName NVARCHAR(50),
        InsuredCode VARCHAR(20),
        NationalCode VARCHAR(12),
        InsuredName NVARCHAR(50),
        InsuredLastName NVARCHAR(50),
        BirthDate DATE,
        Mobile VARCHAR(15),
        MedicationNo VARCHAR(10),
        MedicationName NVARCHAR(100),
        MedicationLastName NVARCHAR(100),
        SpecialtyCode VARCHAR(3),
        SpecialtyName NVARCHAR(45),
        SpecialtyStatus NVARCHAR(10),
        ValidityDate DATE,
        ReceptionDate DATE,
        TurnNo SMALLINT
    );

    BEGIN TRY
        BEGIN TRAN;
        ------------------------------------------ Fetch Warehouse Info
        SET @StatusCode = 100;
        SET @StatusMsg = N'کد انبار ورودي نا معتبر مي باشد';
        SELECT @WarehouseName = [Name]
        FROM dbo.AnbarId
        WHERE A_Code = @WarehouseCode;
        IF ISNULL(@WarehouseName, '0') = '0'
        BEGIN
            RAISERROR(@StatusMsg, 16, 1);
        END;
        ------------------------------------------ Check Exists Tmp DrugH
        SET @StatusCode = 101;
        SET @StatusMsg = N'شماره فاکتور داروخانه معتبر نمي باشد';
        IF NOT EXISTS (SELECT 1 FROM dbo.TmpDrugH WHERE Id_Havaleh = @PharmacyId)
        BEGIN
            RAISERROR(@StatusMsg, 16, 1);
        END;
        ------------------------------------------ Fetch TTAC Prescription Info
        SET @StatusCode = 102;
        SET @StatusMsg = N'هيچ نسخه اي با کد رهگيري وارد شده يافت نشد';
        IF NOT EXISTS
        (
            SELECT 1
            FROM dbo.TTACPrescription
            WHERE TrackingCode = @TrackingCode
        )
        BEGIN
            RAISERROR(@StatusMsg, 16, 1);
        END;
        SELECT @Id = Id
        FROM dbo.TTACPrescription
        WHERE TrackingCode = @TrackingCode;
        ------------------------------------------ Update Tmp DrugH From TTAC Prescription Info
        SET @StatusCode = 103;
        SET @StatusMsg = N'خطا در آپديت هدر نسخه';

        UPDATE dbo.TmpDrugH
        SET PrescriptionType = 3,
            PrescriptionId = @Id
        WHERE Id_Havaleh = @PharmacyId;
        ------------------------------------------ Delete Existing Row Info
        DELETE FROM dbo.TmpDrugHavaleh
        WHERE Id_Havaleh = @PharmacyId;
        ------------------------------------------ Prescription Header Insert Into Cashe Table
        SET @StatusCode = 104;
        SET @StatusMsg = N'خطا در تبديل اقلام به حافظه موقت';

        WITH TTAC
        AS (SELECT @Id Id,
                   H.InsuranceCode,
                   O.Sa_Name InsuranceName,
                   H.InsuredCode,
                   --H.NationalCode InsuredCode,
                   H.NationalCode NationalCode,
                   P.Name InsuredName,
                   P.Family InsuredLastName,
                   P.BirthDate,
                   P.Mobile,
                   H.MedicationNo,
                   M.Name MedicationName,
                   M.Family MedicationLastName,
                   G.Grade_Code SpecialtyCode,
                   G.Grade_Name SpecialtyName,
                   SpecialtyStatus = CASE
                                         WHEN G.[Status] = 1 THEN
                                             N'عمومي'
                                         WHEN G.[Status] = 2 THEN
                                             N'متخصص'
                                         WHEN G.[Status] = 3 THEN
                                             N'فوق تخصص'
                                         WHEN G.[Status] = 4 THEN
                                             N'ماما'
                                         WHEN G.[Status] = 5 THEN
                                             N'دندانپزشک'
                                         WHEN G.[Status] = 6 THEN
                                             N'اتباع'
                                         ELSE
                                             N'عمومي'
                                     END,
                   CAST(GETDATE() AS DATE) ValidityDate,
                   CAST(H.CreatedOn AS DATE) ReceptionDate,
                   H.TurnNo
            FROM dbo.TTACPrescription H
                JOIN dbo.Sahmiyeh O
                    ON O.Sazman_Code = H.InsuranceCode
                JOIN dbo.Doctor M
                    ON M.Nezam_No = H.MedicationNo
                JOIN dbo.Grade G
                    ON G.Grade_Code = M.Grade_Code
                JOIN dbo.Notebimeh P
                    ON P.Note_Code = H.InsuredCode
                       AND P.Meli_Card_No = H.NationalCode
                       AND P.Sazman_Code = H.InsuranceCode
            WHERE H.TrackingCode = @TrackingCode)
        INSERT INTO @Header
        SELECT TTAC.Id,
               TTAC.InsuranceCode,
               TTAC.InsuranceName,
               TTAC.InsuredCode,
               TTAC.NationalCode,
               TTAC.InsuredName,
               TTAC.InsuredLastName,
               TTAC.BirthDate,
               TTAC.Mobile,
               TTAC.MedicationNo,
               TTAC.MedicationName,
               TTAC.MedicationLastName,
               TTAC.SpecialtyCode,
               TTAC.SpecialtyName,
               TTAC.SpecialtyStatus,
               TTAC.ValidityDate,
               TTAC.ReceptionDate,
               TTAC.TurnNo
        FROM TTAC;
        --UPDATE dbo.TmpDrugH
        --SET Note_Code = TTAC.NationalCode,
        --    Nezam_No = TTAC.MedicationNo,
        --    Grade_Code = TTAC.GradeCode,
        --    Trans_Name = TTAC.WarehouseName,
        --    Resive_Name = TTAC.FullName,
        --    Sazman_Code = TTAC.InsuranceCode,
        --    Tarikh_Etebar = TTAC.ValidityDate,
        --    Tarikh_Paziresh = TTAC.ReceptionDate,
        --    Tarikh_Noskheh = TTAC.ReceptionDate,
        --    NumberQueue = TTAC.TurnNo,
        --    PrescriptionType = TTAC.PrescriptionType,
        --    PrescriptionId = TTAC.PrescriptionId,
        --    Page_No = TTAC.PageNo
        --FROM TTAC
        --WHERE Id_Havaleh = @PharmacyId;
        ------------------------------------------ Insert Into Tmp DrugHavaleh  From TTAC Prescription Detail
        SET @StatusCode = 105;
        SET @StatusMsg = N'خطا در اضافه کردن اقلام نسخه';

        WITH TTACDetail
        AS (SELECT @PharmacyId PharmacyId,
                   @WarehouseCode WarehouseCode,
                   '10000' PatientCode,
                   D.RowIndex,
                   D.GoodsCode,
                   CASE
                       WHEN D.InsurerAmount > 0
                            AND D.DeliverCount > D.CoverCount THEN
                           D.CoverCount
                       ELSE
                           D.DeliverCount
                   END DeliverCount,
                   D.NextReferDay,
                   CASE
                       WHEN D.DifferenceAmount > 0
                            AND D.DeliverCount = D.CoverCount THEN
                           ROUND((D.TotalAmount - D.DifferenceAmount) / D.DeliverCount, 6)
                       WHEN D.DifferenceAmount > 0
                            AND D.DeliverCount <> D.CoverCount THEN
                           ROUND(
                                    (D.TotalAmount - ((D.DeliverCount - D.CoverCount) * D.SalesPriceAmount)
                                     - D.DifferenceAmount
                                    ) / D.CoverCount,
                                    6
                                )
                       ELSE
                           D.SalesPriceAmount
                   END SalesPriceAmount,
                   D.SalesPriceAmount MainPriceAmount,
                   D.CheckInventory,
                   D.DifferenceAmount,
                   D.IsAttached,
                   W.Price_Kharid PurchasePrice,
                   100 - D.Franshiz InsuredFranshiz,
                   D.StatusCode,
                   1 HasSerial,
                   0 IsMakeable,
                   0 HasAttachment
            FROM dbo.TTACPrescriptionDetail D
                JOIN dbo.Anbar W
                    ON W.A_Code = @WarehouseCode
                       AND W.K_Code = D.GoodsCode
            WHERE D.PrescriptionId = @Id)
        INSERT INTO dbo.TmpDrugHavaleh
        (
            Id_Havaleh,
            Sender,
            Reciver,
            Radif,
            K_Code,
            K_Qty1,
            Refer_Day,
            Price_Forosh,
            Main_Price,
            CtrMojodi,
            Tot_Differ,
            Link_Record,
            Price_Kharid,
            BimarPercent,
            Status,
            Serial_Flag,
            Make_Flag,
            Hamrah_Flag
        )
        SELECT TTACDetail.PharmacyId,
               TTACDetail.WarehouseCode,
               TTACDetail.PatientCode,
               TTACDetail.RowIndex,
               TTACDetail.GoodsCode,
               ROUND(TTACDetail.DeliverCount, 3),
               TTACDetail.NextReferDay,
               ROUND(TTACDetail.SalesPriceAmount, 0),
               --->TTACDetail.InsuredAmount,
               TTACDetail.MainPriceAmount,
               TTACDetail.CheckInventory,
               TTACDetail.DifferenceAmount,
               TTACDetail.IsAttached,
               TTACDetail.PurchasePrice,
               TTACDetail.InsuredFranshiz,
               TTACDetail.StatusCode,
               TTACDetail.HasSerial,
               TTACDetail.IsMakeable,
               TTACDetail.HasAttachment
        FROM TTACDetail;
        ------------------------------------------ Insert Into Tmp DrugHavaleh  From TTAC Prescription Detail => Differ Deliver Count && Cover Count 
        SET @StatusCode = 106;
        SET @StatusMsg = N'خطا در اضافه کردن اقلام نسخه مازاد سقف بيمه تعريف شده';

        WITH TTACDetail
        AS (SELECT @PharmacyId PharmacyId,
                   @WarehouseCode WarehouseCode,
                   '10000' PatientCode,
                   D.RowIndex,
                   D.GoodsCode,
                   D.DeliverCount - D.CoverCount DeliverCount,
                   D.SalesPriceAmount,
                   0 InsuredAmount,
                   D.CheckInventory,
                   0 DifferenceAmount,
                   D.IsAttached,
                   W.Price_Kharid PurchasePrice,
                   0 InsuredFranshiz,
                   0 StatusCode,
                   1 HasSerial,
                   0 IsMakeable,
                   0 HasAttachment
            FROM dbo.TTACPrescriptionDetail D
                JOIN dbo.Anbar W
                    ON W.A_Code = @WarehouseCode
                       AND W.K_Code = D.GoodsCode
            WHERE D.PrescriptionId = @Id
                  AND D.InsurerAmount > 0
                  AND D.DeliverCount > D.CoverCount)
        INSERT INTO dbo.TmpDrugHavaleh
        (
            Id_Havaleh,
            Sender,
            Reciver,
            Radif,
            K_Code,
            K_Qty1,
            Price_Forosh,
            Main_Price,
            CtrMojodi,
            Tot_Differ,
            Link_Record,
            Price_Kharid,
            BimarPercent,
            Status,
            Serial_Flag,
            Make_Flag,
            Hamrah_Flag
        )
        SELECT *
        FROM TTACDetail;

        ------------------------------------------  Load Header Info 

        SET @StatusCode = 1;
        SET @StatusMsg = N'اطلاعات نسخه با موفقيت فراخواني شد';
        SELECT @StatusCode StatusCode,
               @StatusMsg StatusMsg;

        SELECT TTAC.Id,
               TTAC.InsuranceCode,
               TTAC.InsuranceName,
               TTAC.InsuredCode,
               TTAC.NationalCode,
               TTAC.InsuredName,
               TTAC.InsuredLastName,
               dbo.MiladiToShamsi(TTAC.BirthDate) BirthDate,
               TTAC.Mobile,
               TTAC.MedicationNo,
               TTAC.MedicationName,
               TTAC.MedicationLastName,
               TTAC.SpecialtyCode,
               TTAC.SpecialtyName,
               TTAC.SpecialtyStatus,
               dbo.MiladiToShamsi(TTAC.ValidityDate) ValidityDate,
               dbo.MiladiToShamsi(TTAC.ReceptionDate) ReceptionDate,
               TTAC.TurnNo
        FROM @Header TTAC;

        COMMIT TRAN;
    END TRY
    BEGIN CATCH
        SELECT @StatusCode StatusCode,
               @StatusMsg StatusMsg;
        --RAISERROR(@StatusMsg, 16, 1);
        PRINT ERROR_MESSAGE();
        ROLLBACK TRAN;
    END CATCH;
END;