CREATE PROC 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,
       MTIB.Code InsuranceBoxCode,
       InsuranceCode = CASE
                           WHEN MTIB.Code IS NULL
                                OR MTIB.Code = '' THEN
                               MTI.Code
                           ELSE
                               MTIB.Term
                       END,
	   dh.Note_Code InsuredNumber
FROM dbo.DrugH dh WITH (NOLOCK)
    JOIN dbo.Notebimeh n WITH(NOLOCK)
        ON n.Note_Code = dh.Note_Code
           AND n.Sazman_Code = dh.Sazman_Code
    JOIN dbo.Doctor d WITH(NOLOCK)
        ON d.Nezam_No = dh.Nezam_No
    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
    LEFT JOIN @MedicationTerminology MTIB
        ON MTIB.Type = 4
           AND dh.Sazman_Code = MTIB.Alias
    LEFT JOIN @MedicationTerminology MTI
        ON MTI.Alias = dh.Sazman_Code
           AND MTI.Type = 3
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'تاريخ تولد نامعتبر '   + 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'کد ملي وارد نشده' + 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'کد نظام پزشکي مسئول فني وارد نشده'+ 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'نام پزشک وارد نشده' + 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'نام خانوادگي پزشک وارد نشده' + 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'نام خانوادگي مسئول فني وارد نشده'+ 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'نام مسئول فني وارد نشده' + CHAR(13)+CHAR(10)
END 
IF ((SELECT CASE WHEN ISNULL([@PIS].InsuranceBoxCode, 'empty') = 'empty' OR ([@PIS].InsuranceBoxCode = '') THEN 'empty' ELSE [@PIS].InsuranceBoxCode end FROM @PIS) = 'empty')
BEGIN 
    SET @ErrorNumber = @ErrorNumber +1 
	SET @Message = @Message + CAST( @ErrorNumber AS VARCHAR(3)) + ' - ' + 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'بيمه سپاس مشخص نشده' + 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'کد ملي نسخه پيچ وارد نشده' + 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'کد نظام پزشکي مسئول فني وارد نشده' + 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'شماره دفترچه بيمار وارد نشده ' + 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'نام کاربر نسخه پيچ وارد نشده '+ 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'نام خانوادگي کاربر نسخه پيچ وارد نشده ' + 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)  + ' در اقلام نسخه رديف شماره ' +    CAST(PD.Radif AS VARCHAR(3)) + ' : '+ 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 + '.'--- [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;
