CREATE PROC [dbo].[PISCheckPrescription] @PrescriptionId NUMERIC
AS
DECLARE @MedicationTerminology TABLE
(
    [Code] [VARCHAR](20) NOT NULL,
    [Alias] [VARCHAR](10) NULL,
    [Term] [NVARCHAR](MAX) NULL,
    [PersianMeaning] [NVARCHAR](MAX) NULL,
    [TerminologyID] [NVARCHAR](150) NULL,
    [Type] [TINYINT] NOT NULL
);

INSERT INTO @MedicationTerminology
SELECT [Code],
       [Alias],
       [Term],
       [PersianMeaning],
       [TerminologyID],
       [Type]
FROM MedicationTerminology WITH (NOLOCK)
WHERE Type IN ( 1, 2, 3, 4, 5 );

DECLARE @PIS TABLE
(
    BirthDate INT,
    NationalCode VARCHAR(13),
    MedicalCode NVARCHAR(13),
    PrescriberName NVARCHAR(20),
    PrescriberLastName NVARCHAR(35),
    Mobile VARCHAR(15),
    Tel VARCHAR(15),
    PrescribingPhysiciansFirstName NVARCHAR(30),
    PrescribingPhysiciansLastName NVARCHAR(60),
    PrescribingPhysiciansId VARCHAR(7),
    PharmacyTechniciansFirstName NVARCHAR(30),
    PharmacyTechniciansLastName NVARCHAR(60),
    PharmacyTechniciansNationalCode VARCHAR(13),
    InsuranceBoxCode VARCHAR(20),
    InsuranceCode VARCHAR(20),
    InsuredNumber VARCHAR(20)
);

INSERT INTO @PIS
SELECT CAST(LEFT(dbo.MiladiToShamsi(N.BirthDate), 4) AS INT) BirthDate,
       N.Meli_Card_No,
       dh.Nezam_No,
       d.Name,
       d.Family,
       N.Mobile,
       N.Tel,
       PH.Name PrescribingPhysiciansFirstName,
       PH.Family PrescribingPhysiciansLastName,
       PH.MedicalCode PrescribingPhysiciansId,
       PT.Name PharmacyTechniciansFirstName,
       PT.Family PharmacyTechniciansLastName,
       PT.NationalCode PharmacyTechniciansNationalCode,
       InsuranceBoxCode = CASE
                              WHEN MTINS.Type = 4 THEN
                                  MTINS.Code
                              ELSE
                                  NULL
                          END,
       InsuranceCode = CASE
                           WHEN MTINS.Type = 3 THEN
                               MTINS.Code
                           ELSE
                               MTINS.Term
                       END,
       dh.Note_Code InsuredNumber
FROM dbo.DrugH dh WITH (NOLOCK)
    JOIN dbo.Notebimeh N
        ON N.Note_Code = dh.Note_Code
           AND N.Sazman_Code = dh.Sazman_Code
    JOIN dbo.Doctor d
        ON d.Nezam_No = dh.Nezam_No
    LEFT JOIN dbo.PISReceivedPrescription PISP
        ON PISP.Id = dh.Id
    LEFT JOIN dbo.PISSenderPrescription SP
        ON SP.Id = PISP.PreviousSenderId
    JOIN dbo.Sahmiyeh Ins
        ON Ins.Sazman_Code = dh.Sazman_Code
    LEFT JOIN @MedicationTerminology MTINS
        ON MTINS.Code = Ins.PISCode
           AND MTINS.Type = Ins.PISCodeType
    LEFT JOIN dbo.DefinePharmacyTechnicalAndPharmacist PH
        ON PH.UserName = dh.CheckU
           AND PH.[Status] = 0
    LEFT JOIN dbo.DefinePharmacyTechnicalAndPharmacist PT
        ON PT.UserName = dh.[User_id]
           AND PT.[Status] = 1
WHERE dh.Id_Havaleh = @PrescriptionId;

DECLARE @ErrorNumber INT = 0;
DECLARE @Message NVARCHAR(MAX) = N' ';

IF ((SELECT ISNULL([@PIS].BirthDate, 0) FROM @PIS) = 0)
BEGIN
    SET @ErrorNumber = @ErrorNumber + 1;
    SET @Message = @Message + CAST(@ErrorNumber AS VARCHAR(3)) + N' - ' + N'تاريخ تولد نامعتبر ' + CHAR(13) + CHAR(10);
END;

IF (
   (
       SELECT CASE
                  WHEN ISNULL([@PIS].NationalCode, 'empty') = 'empty'
                       OR ([@PIS].NationalCode = '') THEN
                      'empty'
                  ELSE
                      [@PIS].NationalCode
              END
       FROM @PIS
   ) = 'empty'
   )
BEGIN
    SET @ErrorNumber = @ErrorNumber + 1;
    SET @Message = @Message + CAST(@ErrorNumber AS VARCHAR(3)) + N' - ' + N'کد ملي وارد نشده' + CHAR(13) + CHAR(10);
END;
IF (
   (
       SELECT CASE
                  WHEN ISNULL([@PIS].MedicalCode, 'empty') = 'empty'
                       OR ([@PIS].MedicalCode = '') THEN
                      'empty'
                  ELSE
                      [@PIS].MedicalCode
              END
       FROM @PIS
   ) = 'empty'
   )
BEGIN
    SET @ErrorNumber = @ErrorNumber + 1;
    SET @Message
        = @Message + CAST(@ErrorNumber AS VARCHAR(3)) + N' - ' + N'کد نظام پزشکي مسئول فني وارد نشده' + CHAR(13)
          + CHAR(10);
END;
IF (
   (
       SELECT CASE
                  WHEN ISNULL([@PIS].PrescriberName, 'empty') = 'empty'
                       OR ([@PIS].PrescriberName = '') THEN
                      'empty'
                  ELSE
                      [@PIS].PrescriberName
              END
       FROM @PIS
   ) = 'empty'
   )
BEGIN
    SET @ErrorNumber = @ErrorNumber + 1;
    SET @Message = @Message + CAST(@ErrorNumber AS VARCHAR(3)) + N' - ' + N'نام پزشک وارد نشده' + CHAR(13) + CHAR(10);
END;
IF (
   (
       SELECT CASE
                  WHEN ISNULL([@PIS].PrescriberLastName, 'empty') = 'empty'
                       OR ([@PIS].PrescriberLastName = '') THEN
                      'empty'
                  ELSE
                      [@PIS].PrescriberLastName
              END
       FROM @PIS
   ) = 'empty'
   )
BEGIN
    SET @ErrorNumber = @ErrorNumber + 1;
    SET @Message
        = @Message + CAST(@ErrorNumber AS VARCHAR(3)) + N' - ' + N'نام خانوادگي پزشک وارد نشده' + CHAR(13) + CHAR(10);
END;
IF (
   (
       SELECT CASE
                  WHEN ISNULL([@PIS].PrescriberLastName, 'empty') = 'empty'
                       OR ([@PIS].PrescriberLastName = '') THEN
                      'empty'
                  ELSE
                      [@PIS].PrescriberLastName
              END
       FROM @PIS
   ) = 'empty'
   )
BEGIN
    SET @ErrorNumber = @ErrorNumber + 1;
    SET @Message
        = @Message + CAST(@ErrorNumber AS VARCHAR(3)) + N' - ' + N'نام خانوادگي مسئول فني وارد نشده' + CHAR(13)
          + CHAR(10);
END;
IF (
   (
       SELECT CASE
                  WHEN ISNULL([@PIS].PrescribingPhysiciansFirstName, 'empty') = 'empty'
                       OR ([@PIS].PrescribingPhysiciansFirstName = '') THEN
                      'empty'
                  ELSE
                      [@PIS].PrescribingPhysiciansFirstName
              END
       FROM @PIS
   ) = 'empty'
   )
BEGIN
    SET @ErrorNumber = @ErrorNumber + 1;
    SET @Message
        = @Message + CAST(@ErrorNumber AS VARCHAR(3)) + N' - ' + N'نام مسئول فني وارد نشده' + CHAR(13) + CHAR(10);
END;
IF (
   (
       SELECT CASE
                  WHEN ISNULL([@PIS].InsuranceCode, 'empty') = 'empty'
                       OR ([@PIS].InsuranceCode = '') THEN
                      'empty'
                  ELSE
                      [@PIS].InsuranceCode
              END
       FROM @PIS
   ) = 'empty'
   )
BEGIN
    SET @ErrorNumber = @ErrorNumber + 1;
    SET @Message = @Message + CAST(@ErrorNumber AS VARCHAR(3)) + N' - ' + N'بيمه سپاس مشخص نشده' + CHAR(13) + CHAR(10);
END;
IF (
   (
       SELECT CASE
                  WHEN ISNULL([@PIS].PharmacyTechniciansNationalCode, 'empty') = 'empty'
                       OR ([@PIS].PharmacyTechniciansNationalCode = '') THEN
                      'empty'
                  ELSE
                      [@PIS].PharmacyTechniciansNationalCode
              END
       FROM @PIS
   ) = 'empty'
   )
BEGIN
    SET @ErrorNumber = @ErrorNumber + 1;
    SET @Message
        = @Message + CAST(@ErrorNumber AS VARCHAR(3)) + N' - ' + N'کد ملي نسخه پيچ وارد نشده' + CHAR(13) + CHAR(10);
END;
IF (
   (
       SELECT CASE
                  WHEN ISNULL([@PIS].PrescribingPhysiciansId, 'empty') = 'empty'
                       OR ([@PIS].PrescribingPhysiciansId = '') THEN
                      'empty'
                  ELSE
                      [@PIS].PrescribingPhysiciansId
              END
       FROM @PIS
   ) = 'empty'
   )
BEGIN
    SET @ErrorNumber = @ErrorNumber + 1;
    SET @Message
        = @Message + CAST(@ErrorNumber AS VARCHAR(3)) + N' - ' + N'کد نظام پزشکي مسئول فني وارد نشده' + CHAR(13)
          + CHAR(10);
END;
IF (
   (
       SELECT CASE
                  WHEN ISNULL([@PIS].InsuredNumber, 'empty') = 'empty'
                       OR ([@PIS].InsuredNumber = '') THEN
                      'empty'
                  ELSE
                      [@PIS].InsuredNumber
              END
       FROM @PIS
   ) = 'empty'
   )
BEGIN
    SET @ErrorNumber = @ErrorNumber + 1;
    SET @Message
        = @Message + CAST(@ErrorNumber AS VARCHAR(3)) + N' - ' + N'شماره دفترچه بيمار وارد نشده ' + CHAR(13) + CHAR(10);
END;
IF (
   (
       SELECT CASE
                  WHEN ISNULL([@PIS].PharmacyTechniciansFirstName, 'empty') = 'empty'
                       OR ([@PIS].PharmacyTechniciansFirstName = '') THEN
                      'empty'
                  ELSE
                      [@PIS].PharmacyTechniciansFirstName
              END
       FROM @PIS
   ) = 'empty'
   )
BEGIN
    SET @ErrorNumber = @ErrorNumber + 1;
    SET @Message
        = @Message + CAST(@ErrorNumber AS VARCHAR(3)) + N' - ' + N'نام کاربر نسخه پيچ وارد نشده ' + CHAR(13) + CHAR(10);
END;
IF (
   (
       SELECT CASE
                  WHEN ISNULL([@PIS].PrescribingPhysiciansLastName, 'empty') = 'empty'
                       OR ([@PIS].PrescribingPhysiciansLastName = '') THEN
                      'empty'
                  ELSE
                      [@PIS].PrescribingPhysiciansLastName
              END
       FROM @PIS
   ) = 'empty'
   )
BEGIN
    SET @ErrorNumber = @ErrorNumber + 1;
    SET @Message
        = @Message + CAST(@ErrorNumber AS VARCHAR(3)) + N' - ' + N'نام خانوادگي کاربر نسخه پيچ وارد نشده ' + CHAR(13)
          + CHAR(10);
END;

DECLARE @PISDetail TABLE
(
    RowNumber SMALLINT,
    IRC VARCHAR(20),
    FrequencyCode VARCHAR(9),
    FrequencyName NVARCHAR(MAX),
    RouteCode VARCHAR(9),
    RoteName NVARCHAR(MAX),
    GenericName NVARCHAR(200)
);

SELECT @Message
    = @Message + CHAR(10) + N' در اقلام نسخه رديف شماره ' + CAST(PD.Radif AS VARCHAR(3)) + N' : ' + CHAR(13) + CHAR(10)
      + CASE
            WHEN ISNULL(G.Barcode, 'empty') = 'empty'
                 OR G.Barcode = '' THEN
                ' فاقد کد ( IRC )  -'
            WHEN LEN(G.Barcode) < 16 THEN
                '  کد ( IRC ) نامعتبر  - '
            ELSE
                ' '
        END + CASE
                  WHEN ISNULL(PD.FrequencyCode, 'empty') = 'empty'
                       OR PD.FrequencyCode = '' THEN
                      ' فاقد کد تواتر مصرف  -'
                  ELSE
                      ' '
              END + CASE
                        WHEN ISNULL(MTF.Term, 'empty') = 'empty'
                             OR MTF.Term = '' THEN
                            ' فاقد تواتر مصرف  -'
                        ELSE
                            ' '
                    END + CASE
                              WHEN ISNULL(PD.RouteCode, 'empty') = 'empty'
                                   OR PD.RouteCode = '' THEN
                                  ' فاقد کد طريقه مصرف  -'
                              ELSE
                                  ' '
                          END + CASE
                                    WHEN ISNULL(MTR.Term, 'empty') = 'empty'
                                         OR MTR.Term = '' THEN
                                        ' فاقد طريقه مصرف '
                                    ELSE
                                        ' '
                                END + N'.' --- [Message]
FROM dbo.DrugHavaleh PD WITH (NOLOCK)
    JOIN dbo.KalaId G WITH (NOLOCK)
        ON G.K_Code = PD.K_Code
    LEFT JOIN @MedicationTerminology MTF
        ON MTF.Code = PD.FrequencyCode
           AND MTF.Type = 1
    LEFT JOIN @MedicationTerminology MTR
        ON MTR.Code = PD.RouteCode
           AND MTR.Type = 2
    LEFT JOIN @MedicationTerminology MTS
        ON MTS.Code = G.ShapeCode
           AND MTS.Type = 5
WHERE PD.Id_Havaleh = @PrescriptionId
      AND
      (
          CASE
              WHEN ISNULL(G.Barcode, 'empty') = 'empty'
                   OR (G.Barcode = '') THEN
                  'empty'
          END = 'empty'
          OR CASE
                 WHEN ISNULL(PD.FrequencyCode, 'empty') = 'empty'
                      OR (PD.FrequencyCode = '') THEN
                     'empty'
             END = 'empty'
          OR CASE
                 WHEN ISNULL(MTF.Term, 'empty') = 'empty'
                      OR (MTF.Term = '') THEN
                     'empty'
             END = 'empty'
          OR CASE
                 WHEN ISNULL(PD.RouteCode, 'empty') = 'empty'
                      OR (PD.RouteCode = '') THEN
                     'empty'
             END = 'empty'
          OR CASE
                 WHEN ISNULL(MTR.Term, 'empty') = 'empty'
                      OR (MTR.Term = '') THEN
                     'empty'
             END = 'empty'
      );
SET @Message = RTRIM(LTRIM(@Message));
SELECT @Message [ErrorMessage];
IF @Message IS NULL
   OR @Message = ''
BEGIN
    UPDATE dbo.PISSenderPrescription
    SET Status = 1
    WHERE PrescriptionId = @PrescriptionId;
END;
ELSE
BEGIN
    UPDATE dbo.PISSenderPrescription
    SET [Status] = 3,
        [ErrorDescription] = @Message,
        [ResponseDate ] = GETDATE(),
        [ErrorSolution] = N'خطاي قبل از ارسال به سامانه نسخه الکترونيک ، لطفا ليست خطاي ذکر شده را برطرف کرده سپس ارسال مجدد را اجرا کنيد',
        [ErrorMessage] = 'Before send prescription checked by Pyramid Drug Software and found error.'
    WHERE PrescriptionId = @PrescriptionId;
END;