<mysql_data>
</mysql_data>
<fbsql_data dest_pharm_id="0">
/*
<nzok_update_date>16.04.2023</nzok_update_date> 
<fix_index>3</fix_index> 
<comment>СЕСПА - промяна настройки за отчетност</comment> 
*/
SET TERM ^ ;

create or alter procedure SP_SESPA_INIT_REPORT_DATA (
    FROM_DATE timestamp,
    TO_DATE timestamp,
    DEL_TYPE_ID integer)
as
declare variable ALOCAL_CLIENT_ID varchar(255);
declare variable ACLEAN_CODE_ARTICLE_ID integer;
declare variable ACURRENT_NUMBER integer;
declare variable ASESPA_CODE_ID integer;
BEGIN
  FOR
    SELECT
      SC.ARTICLE_ID
    FROM SESPA_CODE SC
    GROUP BY (SC.ARTICLE_ID)
    HAVING COUNT(*) > 1
    INTO
      :ACLEAN_CODE_ARTICLE_ID
  DO
  BEGIN
    ACURRENT_NUMBER = 1;
    FOR
      SELECT
        SC.ID
      FROM SESPA_CODE SC
      WHERE (SC.ARTICLE_ID = :ACLEAN_CODE_ARTICLE_ID)
      INTO
        :ASESPA_CODE_ID
    DO
    BEGIN
      IF (ACURRENT_NUMBER > 1) THEN
        DELETE FROM SESPA_CODE SC WHERE SC.ID = :ASESPA_CODE_ID;

      ACURRENT_NUMBER = ACURRENT_NUMBER + 1;
    END
  END

  SELECT
    SP.ARES
  FROM SP_SESPA_GET_LOCAL_CLIENT_ID SP
  INTO
    :ALOCAL_CLIENT_ID;

  DELETE FROM SESPA_REPORT_NS_DATA SRND WHERE SRND.LOCAL_CLIENT_ID = :ALOCAL_CLIENT_ID;

  INSERT INTO SESPA_REPORT_NS_DATA (ARTICLE_ID, QUANTITY_LEFT, LOCAL_CLIENT_ID)
  SELECT
    SP.ARTICLE_ID,
    SP.AVAILABLE_QUANTITY,
    :ALOCAL_CLIENT_ID
  FROM SP_SPR_SESPA_ARTS_NACH_SALDO(:FROM_DATE, :TO_DATE, :DEL_TYPE_ID) SP;

  DELETE FROM SESPA_REPORT_TS_DATA SRTS WHERE SRTS.LOCAL_CLIENT_ID = :ALOCAL_CLIENT_ID;

  INSERT INTO SESPA_REPORT_TS_DATA 
    (ARTICLE_ID, DOCUMENT_NO, DOCUMENT_DATE, GIVEN_QUANTITY,
     QUANTITY, PARCEL_NO, DOC_TYPE_INFO_ID,
     CUSTOMER_ID, SUPPLIER_ID, DELIVERY_ID,
    INV_COR_TYPE_ID, DELIVERY_ITEM_ID, INV_COR_ITEM_ID,
    SALE_ITEM_ID, SALE_COR_ITEM_ID, PARCEL_ID,
    LOCAL_CLIENT_ID)
  SELECT
    SP1.ARTICLE_ID, SP1.DOCUMENT_NO_SALE_DELIV_INV_COR, SP1.DOCUMENT_DATE, SP1.GIVEN_QUANTITY,
    SP1.QUANTITY, SP1.PARCEL_NO, SP1.DOCUMENT_TYPE_INFO_ID,
    SP1.CUSTOMER_ID, SP1.SUPPLIER_ID, SP1.DELIVERY_ID,
    SP1.INV_COR_TYPE_ID, SP1.DELIVERY_ITEM_ID,  SP1.INV_COR_ITEM_ID,
    SP1.SALE_ITEM_ID, SP1.SALE_COR_ITEM_ID, SP1.PARCEL_ID,
    :ALOCAL_CLIENT_ID
  FROM SP_SPR_SESPA_KARTON_MULTI(:FROM_DATE, :TO_DATE) SP1;
END^

SET TERM ; ^

SET TERM ^ ;

create or alter procedure SP_SPR_SESPA_DATA_CHECK1 (
    FROM_DATE timestamp,
    TO_DATE timestamp,
    DEL_TYPE_ID integer,
    TRADE_OBJECT_TYPE integer)
returns (
    SUPPLIER_ID integer,
    CUSTOMER_ID integer,
    NOM_TYPE integer,
    NOM_DESCRIPTION varchar(100),
    ISSUE_DESCRIPTION varchar(100),
    CONTRAGENT_NAME varchar(100))
as
declare variable ACOMPANY_POSTCODE varchar(10);
BEGIN

  ACOMPANY_POSTCODE = NULL;
  SELECT
    FIRST 1
      SS.SETTING_DATA
  FROM SESPA_SETTINGS SS
  WHERE (SS.SETTING_NAME = 'CompanyPostCode')
  INTO
    :ACOMPANY_POSTCODE;

  if (:ACOMPANY_POSTCODE = '') then
    ACOMPANY_POSTCODE = NULL;

  if (ACOMPANY_POSTCODE IS NULL) then
  BEGIN
    SUPPLIER_ID = -1;
    CUSTOMER_ID = -1;
    NOM_TYPE = 0;
    NOM_DESCRIPTION = 'Настройки';
    ISSUE_DESCRIPTION = 'Липсват данни за пощенски код на обекта';
    CONTRAGENT_NAME = 'Настройки СЕСПА';
    SUSPEND;
  END

  /*
  P_RECIPIENT_CODE IS NULL (Dostavka bez SUPPLIER.BULSTAT)
  -- prodajba vinagi e na 000000000

  Vrushta tip Dostavchik -> SUPPLIER_ID (CUSTOMER_ID IS NULL) -> (Problem lipsva EIK na Dostavchik)

  =======================
  Wholesaler

  -- kum momenta dostavki ne se otchitat
  Ako nqma ukazan Tip na klienta (P_TRANSACTION_TYPE IS NULL) -> CUSTOMER_ID (Problem lipsva Tip na klienta)
  Ako nqma P_RECIPIENT_CODE IS NULL -> Vurshta CUSTOMER_ID i problem (Lipsvat danni za licenz / EIK na klienta)
  */

  if (:TRADE_OBJECT_TYPE = 0) then
  BEGIN
      /* Pharmacy */
      FOR
        SELECT
          DISTINCT(SP.SUPPLIER_ID) SUPPLIER_ID,
          CAST(NULL AS INTEGER) CUSTOMER_ID,
          CAST(1 AS INTEGER) NOM_TYPE,
          CAST('Доставчици' AS VARCHAR(100)) NOM_DESCRIPTION,
          CAST('Липсват данни за ЕИК на доставчик' AS VARCHAR(100)) ISSUE_DESCRIPTION
        FROM SP_SPR_SESPA_REPORT1(:FROM_DATE, :TO_DATE, :DEL_TYPE_ID, 0) SP
        WHERE (SP.P_RECIPIENT_CODE IS NULL)
          AND (((SP.DOC_TYPE_INFO_ID = 1) AND (SP.P_TRANSACTION_TYPE <> 26) AND (SP.P_TRANSACTION_TYPE <> 37)) OR (SP.P_TRANSACTION_TYPE IN (27)))
          AND (SP.SUPPLIER_ID <> -100)

        UNION ALL

        SELECT
          SP1.SUPPLIER_ID,
          SP1.CUSTOMER_ID,
          SP1.NOM_TYPE,
          SP1.NOM_DESCRIPTION,
          SP1.ISSUE_DESCRIPTION
        FROM (
        SELECT
          DISTINCT(SP.CUSTOMER_ID) CUSTOMER_ID,
          CAST(NULL AS INTEGER) SUPPLIER_ID,
          CAST(2 AS INTEGER) NOM_TYPE,
          CAST('Клиенти' AS VARCHAR(100)) NOM_DESCRIPTION,
          CAST('Липсват данни за ЕИК/лиценз на клиент' AS VARCHAR(100)) ISSUE_DESCRIPTION
        FROM SP_SPR_SESPA_REPORT1(:FROM_DATE, :TO_DATE, :DEL_TYPE_ID, 0) SP
        WHERE (SP.P_RECIPIENT_CODE IS NULL) AND ((SP.DOC_TYPE_INFO_ID <> 1) AND (SP.P_TRANSACTION_TYPE NOT IN (27, 26, 37)))
        ) SP1

        UNION ALL

        SELECT
          DISTINCT(SP.SUPPLIER_ID) SUPPLIER_ID,
          CAST(NULL AS INTEGER) CUSTOMER_ID,
          CAST(1 AS INTEGER) NOM_TYPE,
          CAST('Доставчици' AS VARCHAR(100)) NOM_DESCRIPTION,
          CAST('Липсват данни за ПК на доставчик' AS VARCHAR(100)) ISSUE_DESCRIPTION
        FROM SP_SPR_SESPA_REPORT1(:FROM_DATE, :TO_DATE, :DEL_TYPE_ID, 0) SP
        WHERE (SP.P_POSTCODE IS NULL) AND (SP.DOC_TYPE_INFO_ID = 1)
          AND (SP.SUPPLIER_ID <> -100)

        UNION ALL

        SELECT
          DISTINCT(SP.SUPPLIER_ID) SUPPLIER_ID,
          CAST(NULL AS INTEGER) CUSTOMER_ID,
          CAST(1 AS INTEGER) NOM_TYPE,
          CAST('Доставчици' AS VARCHAR(100)) NOM_DESCRIPTION,
          CAST('Липсват данни за пощ. код на доставчик' AS VARCHAR(100)) ISSUE_DESCRIPTION
        FROM SP_SPR_SESPA_REPORT1(:FROM_DATE, :TO_DATE, :DEL_TYPE_ID, 0) SP
        WHERE (SP.P_RCPT_POSTCODE IS NULL) AND (SP.P_TRANSACTION_TYPE IN (27, 42))


      INTO
        :SUPPLIER_ID,
        :CUSTOMER_ID, 
        :NOM_TYPE,
        :NOM_DESCRIPTION,
        :ISSUE_DESCRIPTION
      DO
      BEGIN
        CONTRAGENT_NAME = NULL;
        IF (:NOM_TYPE = 1) THEN
          SELECT
            FIRST 1
            S.NAME
          FROM SUPPLIER S
          WHERE S.ID = :SUPPLIER_ID
          INTO
            :CONTRAGENT_NAME;
    
        SUSPEND;
      END
  END ELSE BEGIN
    /* Wholesaler */
    FOR
    SELECT
      DISTINCT(SP.CUSTOMER_ID) CUSTOMER_ID,
      CAST(NULL AS INTEGER) SUPPLIER_ID,
      CAST(2 AS INTEGER) NOM_TYPE,
      CAST('Клиенти' AS VARCHAR(100)) NOM_DESCRIPTION,
      CAST('Липсват данни за тип на клиента' AS VARCHAR(100)) ISSUE_DESCRIPTION
    FROM SP_SPR_SESPA_REPORT1(:FROM_DATE, :TO_DATE, :DEL_TYPE_ID, 1) SP
    WHERE (SP.P_TRANSACTION_TYPE IS NULL)
    
    UNION ALL
    
    SELECT
      DISTINCT(SP.CUSTOMER_ID) CUSTOMER_ID,
      CAST(NULL AS INTEGER) SUPPLIER_ID,
      CAST(2 AS INTEGER) NOM_TYPE,
      CAST('Клиенти' AS VARCHAR(100)) NOM_DESCRIPTION,
      CAST('Липсват данни за лиценз / ЕИК на клиента' AS VARCHAR(100)) ISSUE_DESCRIPTION
    FROM SP_SPR_SESPA_REPORT1(:FROM_DATE, :TO_DATE, :DEL_TYPE_ID, 1) SP
    WHERE (SP.P_RECIPIENT_CODE IS NULL)
        AND (SP.P_TRANSACTION_TYPE IS NOT NULL)
        AND (SP.P_TRANSACTION_TYPE NOT IN (27, 42, 1, 4))

    UNION ALL

    SELECT
      SP2.CUSTOMER_ID,
      SP2.SUPPLIER_ID,
      SP2.NOM_TYPE,
      SP2.NOM_DESCRIPTION,
      SP2.ISSUE_DESCRIPTION
    FROM (
      SELECT
          DISTINCT(SP.SUPPLIER_ID) SUPPLIER_ID,
          CAST(NULL AS INTEGER) CUSTOMER_ID,
          CAST(1 AS INTEGER) NOM_TYPE,
          CAST('Доставчици' AS VARCHAR(100)) NOM_DESCRIPTION,
          CAST('Липсват данни за лиценз / ЕИК на доставчика' AS VARCHAR(100)) ISSUE_DESCRIPTION
        FROM SP_SPR_SESPA_REPORT1(:FROM_DATE, :TO_DATE, :DEL_TYPE_ID, 1) SP
        WHERE (SP.P_RECIPIENT_CODE IS NULL) AND (SP.P_TRANSACTION_TYPE IS NOT NULL) AND (SP.P_TRANSACTION_TYPE IN (27, 42))

    ) SP2

    /* P_POSTCODE */
    /* всички транзакции са с COMPANY.POST_CODE */
    /* P_RCPT_POSTCODE */
    UNION ALL

    SELECT
      DISTINCT(SP.CUSTOMER_ID) CUSTOMER_ID,
      CAST(NULL AS INTEGER) SUPPLIER_ID,
      CAST(2 AS INTEGER) NOM_TYPE,
      CAST('Клиенти' AS VARCHAR(100)) NOM_DESCRIPTION,
      CAST('Липсват данни за пощенски код на клиента' AS VARCHAR(100)) ISSUE_DESCRIPTION
    FROM SP_SPR_SESPA_REPORT1(:FROM_DATE, :TO_DATE, :DEL_TYPE_ID, 1) SP
    WHERE (SP.P_RCPT_POSTCODE IS NULL)
        AND (SP.P_TRANSACTION_TYPE IS NOT NULL)
        AND (SP.P_TRANSACTION_TYPE NOT IN (16, 38, 43))

    UNION ALL

    SELECT
      SP2.CUSTOMER_ID,
      SP2.SUPPLIER_ID,
      SP2.NOM_TYPE,
      SP2.NOM_DESCRIPTION,
      SP2.ISSUE_DESCRIPTION
    FROM (
      SELECT
        DISTINCT(SP.SUPPLIER_ID) SUPPLIER_ID,
        CAST(NULL AS INTEGER) CUSTOMER_ID,
        CAST(1 AS INTEGER) NOM_TYPE,
        CAST('Доставчици' AS VARCHAR(100)) NOM_DESCRIPTION,
        CAST('Липсват данни за пощенски код на доставчика' AS VARCHAR(100)) ISSUE_DESCRIPTION
      FROM SP_SPR_SESPA_REPORT1(:FROM_DATE, :TO_DATE, :DEL_TYPE_ID, 1) SP
      WHERE (SP.P_RCPT_POSTCODE IS NULL)
        AND (SP.P_TRANSACTION_TYPE IS NOT NULL)
        AND (SP.P_TRANSACTION_TYPE NOT IN (16, 27, 42, 33))

    ) SP2


    INTO
      :CUSTOMER_ID,
      :SUPPLIER_ID,
      :NOM_TYPE,
      :NOM_DESCRIPTION,
      :ISSUE_DESCRIPTION
    DO
    BEGIN
      CONTRAGENT_NAME = NULL;
      IF (:NOM_TYPE = 2) THEN
        SELECT
          FIRST 1
          C.NAME
        FROM CUSTOMER C
        WHERE C.ID = :CUSTOMER_ID
        INTO
          :CONTRAGENT_NAME;

      SUSPEND;
    END
  END
END^

SET TERM ; ^

SET TERM ^ ;

create or alter procedure SP_SPR_SESPA_PERIOD_REPORT (
    FROM_DATE timestamp,
    TO_DATE timestamp,
    DEL_TYPE_ID integer,
    TRADE_OBJECT_TYPE integer)
returns (
    ARTICLE_ID integer,
    P_REPORT_DATE timestamp,
    P_DRUG_CODE varchar(20),
    P_QUANTITY integer,
    P_TRANSACTION_TYPE integer,
    P_AVAILABLE_QTY integer,
    P_BATCH_NO varchar(20),
    P_DRUG_PRODUCT_CODE varchar(50),
    P_COUNTRY varchar(20),
    P_RECIPIENT_CODE varchar(100),
    SUPPLIER_ID integer,
    CUSTOMER_ID integer,
    P_EXPORT_PLANNED_DATE timestamp,
    P_ACTOR_TRN_NO varchar(20),
    PARCEL_ID integer,
    DELIVERY_ITEM_ID integer,
    LOCAL_TABLE_INDEX integer,
    LOCAL_DOCUMENT_ITEM_ID integer,
    SESPA_TRANSACTION_ID varchar(20),
    STATUS integer,
    ERROR_STATUS varchar(50),
    DOC_TYPE_INFO_ID integer,
    DOC_TYPE_NAME varchar(100),
    P_POSTCODE varchar(10),
    P_RCPT_POSTCODE varchar(10),
    P_NOTES varchar(200),
    ATOMIC_FACTOR integer,
    REPORT_DATE_AS_DATE date)
as
BEGIN

    FOR
    SELECT
      SP.ARTICLE_ID,
      SP.P_REPORT_DATE,
      SP.P_DRUG_CODE,
      SP.P_QUANTITY,
      SP.P_TRANSACTION_TYPE,
      SP.P_AVAILABLE_QTY,
      SP.P_BATCH_NO,
      SP.P_DRUG_PRODUCT_CODE,
      SP.P_COUNTRY,
      SP.P_RECIPIENT_CODE,
      SP.SUPPLIER_ID,
      SP.CUSTOMER_ID,
      SP.P_EXPORT_PLANNED_DATE,
      SP.P_ACTOR_TRN_NO,
      SP.PARCEL_ID,
      SP.DELIVERY_ITEM_ID,
      SP.LOCAL_TABLE_INDEX,
      SP.LOCAL_DOCUMENT_ITEM_ID,
      ST.SESPA_TRANSACTION_ID,
      ST.STATUS,
      ST.ERROR_STATUS,
      SP.DOC_TYPE_INFO_ID,
      SP.DOC_TYPE_NAME,
      SP.P_POSTCODE,
      SP.P_RCPT_POSTCODE,
      SP.P_NOTES,
      SP.ATOMIC_FACTOR,
      CAST(SP.P_REPORT_DATE AS DATE)
    FROM SP_SPR_SESPA_REPORT1(:FROM_DATE, :TO_DATE + 1, :DEL_TYPE_ID, :TRADE_OBJECT_TYPE) SP
    LEFT OUTER JOIN SESPA_TRANSACTION ST ON (ST.LOCAL_TABLE_INDEX = SP.LOCAL_TABLE_INDEX) AND (ST.LOCAL_DOCUMENT_ITEM_ID = SP.LOCAL_DOCUMENT_ITEM_ID)
  --  ORDER BY SP.P_REPORT_DATE
    INTO
      :ARTICLE_ID,
      :P_REPORT_DATE,
      :P_DRUG_CODE,
      :P_QUANTITY,
      :P_TRANSACTION_TYPE,
      :P_AVAILABLE_QTY,
      :P_BATCH_NO,
      :P_DRUG_PRODUCT_CODE,
      :P_COUNTRY,
      :P_RECIPIENT_CODE,
      :SUPPLIER_ID,
      :CUSTOMER_ID,
      :P_EXPORT_PLANNED_DATE,
      :P_ACTOR_TRN_NO,
      :PARCEL_ID,
      :DELIVERY_ITEM_ID,
      :LOCAL_TABLE_INDEX,
      :LOCAL_DOCUMENT_ITEM_ID,
      :SESPA_TRANSACTION_ID,
      :STATUS,
      :ERROR_STATUS,
      :DOC_TYPE_INFO_ID,
      :DOC_TYPE_NAME,
      :P_POSTCODE,
      :P_RCPT_POSTCODE,
      :P_NOTES,
      :ATOMIC_FACTOR,
      :REPORT_DATE_AS_DATE
    DO
      SUSPEND;
END^

SET TERM ; ^
</fbsql_data>