Activar Segundos Tiempos en KDS

USE [XETUXPOS]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:      JOSE LOPEZ (Optimizado para Segundos Tiempos)
-- Description: Maneja la lógica de envío de productos a KDS con soporte para prioridades.
-- =============================================
ALTER PROCEDURE [dbo].[SP_POS_PRODUCTS_TO_KDS]
    @idSubOrder INT,
    @changeActiveKdsXetux AS BIT,
    @idStatus INT
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @id_environment INT
    DECLARE @idOrderType INT;
    DECLARE @kds_group_id_orderType AS INT;

    BEGIN TRY
        SELECT top 1 @id_environment = o.environment_id from t_pos_suborder s inner join t_pos_order o on o.order_id = s.order_id where s.suborder_id = @idSubOrder
        select @idOrderType = order_type_id from T_POS_ENVIRONMENT where environment_id = @id_environment
        select @kds_group_id_orderType = ISNULL(group_kds_id, 0) from T_POS_ORDER_TYPE where order_type_id = @idOrderType

        -- PRODUCTOS NORMALES CON PRIORIDAD
        SELECT ISNULL(order_product.preparation_time, 1) preparation_time, product.product_id, item.item_name,
          priority.priority_description as family_name, order_product.quantity,  kds.kds_code,
          ISNULL(order_product.order_product_id_parent_annulation, 0) order_product_id_parent_annulation,
          order_product.order_product_id, groups_KDS.kds_group_id, order_product.seat_number,
          ISNULL(priority.priority_description, '') as priority,
          ISNULL(priority.catdelay, '') as catdelay,
          ISNULL(priority.priority_id, 1) as priority_id, isnull(priority.rgb_color,'#F7F7F7') as rgb_color
      FROM T_POS_ORDER_PRODUCT order_product
          INNER JOIN T_POS_PRODUCT product ON order_product.product_id = product.product_id
          INNER JOIN T_POS_ITEM item ON product.item_id = item.item_id
          INNER JOIN T_POS_PRODUCT_FAMILY product_family ON product.product_id = product_family.product_id AND product_family.is_family_default = 1
          INNER JOIN T_POS_FAMILY family ON product_family.family_id = family.family_id and family.family_type_id = 1
          INNER JOIN T_POS_PRODUCT_KDS_GROUP product_KDS ON product.product_id = product_KDS.product_id
          INNER JOIN T_POS_KDS_GROUP groups_KDS ON product_KDS.kds_group_id = groups_KDS.group_id
          INNER JOIN T_POS_KDS kds ON groups_KDS.kds_id = kds.kds_id
          LEFT  JOIN T_POS_PRIORITY priority ON order_product.product_priority_id = priority.priority_id
      WHERE (iif(order_product.order_product_status_id != 4, ISNULL(order_product.send_to_kds, 0), ISNULL(order_product.send_to_kds_annulation, 0)) = 0
             OR (SELECT suborder_status_id FROM T_POS_SUBORDER WHERE suborder_id = order_product.suborder_id) = 3)
          AND order_product.suborder_id = @idSubOrder
          AND product.enableKDS = 1 AND ISNULL(product.kdsEnvironment, 0) = 0
          AND isnull(product.kdsStation, 0) = 0
          AND order_product.order_product_status_id = iif(@changeActiveKdsXetux = 1, order_product.order_product_status_id, @idStatus)

          UNION ALL

        -- PRODUCTOS POR ESTACIÓN
        SELECT ISNULL(order_product.preparation_time, 1) preparation_time, product.product_id, item.item_name,
                    priority.priority_description as family_name, order_product.quantity,  kds.kds_code,
                    ISNULL(order_product.order_product_id_parent_annulation, 0) order_product_id_parent_annulation,  order_product.order_product_id, groups_KDS.kds_group_id, order_product.seat_number,
          ISNULL(priority.priority_description, '') as priority,
          ISNULL(priority.catdelay, '') as catdelay,
          ISNULL(priority.priority_id, 1) as priority_id, isnull(priority.rgb_color,'#F7F7F7') as rgb_color
            FROM T_POS_ORDER_PRODUCT order_product
          INNER JOIN T_POS_PRODUCT product ON order_product.product_id = product.product_id
          INNER JOIN T_POS_ITEM item ON product.item_id = item.item_id
          INNER JOIN T_POS_PRODUCT_FAMILY product_family ON product.product_id = product_family.product_id AND product_family.is_family_default = 1
          INNER JOIN T_POS_FAMILY family ON product_family.family_id = family.family_id and family.family_type_id = 1
          INNER JOIN T_POS_PRODUCT_KDS_GROUP_STATION kgs ON order_product.product_id = kgs.product_id AND kgs.station_id = order_product.station_id
          INNER JOIN T_POS_KDS_GROUP groups_KDS ON kgs.group_id = groups_KDS.group_id
          INNER JOIN T_POS_KDS kds ON groups_KDS.kds_id = kds.kds_id
          LEFT  JOIN T_POS_PRIORITY priority ON order_product.product_priority_id = priority.priority_id
      WHERE (iif(order_product.order_product_status_id != 4, ISNULL(order_product.send_to_kds, 0), ISNULL(order_product.send_to_kds_annulation, 0)) = 0
             OR (SELECT suborder_status_id FROM T_POS_SUBORDER WHERE suborder_id = order_product.suborder_id) = 3)
          AND order_product.suborder_id = @idSubOrder
          AND product.enableKDS = 1 AND ISNULL(product.kdsEnvironment, 0) = 0 AND isnull(product.kdsStation, 0) = 1
          AND order_product.order_product_status_id = iif(@changeActiveKdsXetux = 1, order_product.order_product_status_id, @idStatus)

          UNION ALL

        -- PRODUCTOS POR AMBIENTE
        SELECT ISNULL(order_product.preparation_time, 1) preparation_time, product.product_id, item.item_name,
          priority.priority_description as family_name, order_product.quantity,  kds.kds_code,
          ISNULL(order_product.order_product_id_parent_annulation, 0) order_product_id_parent_annulation,  order_product.order_product_id, groups_KDS.kds_group_id, order_product.seat_number,
          ISNULL(priority.priority_description, '') as priority,
          ISNULL(priority.catdelay, '') as catdelay,
          ISNULL(priority.priority_id, 1) as priority_id, isnull(priority.rgb_color,'#F7F7F7') as rgb_color
            FROM T_POS_ORDER_PRODUCT order_product
          INNER JOIN T_POS_PRODUCT product ON order_product.product_id = product.product_id
          INNER JOIN T_POS_ITEM item ON product.item_id = item.item_id
          INNER JOIN T_POS_SUBORDER so ON so.suborder_id = order_product.suborder_id
          INNER JOIN T_POS_ORDER O ON o.order_id = so.order_id
          INNER JOIN T_POS_PRODUCT_FAMILY product_family ON product.product_id = product_family.product_id AND product_family.is_family_default = 1
          INNER JOIN T_POS_FAMILY family ON product_family.family_id = family.family_id and family.family_type_id = 1
          INNER JOIN T_POS_KDS_GROUP_ENVIRONMENTS KGEN ON KGEN.environment_id = o.environment_id and KGEN.environment_id = @id_environment
          INNER JOIN T_POS_KDS_GROUP groups_KDS ON KGEN.group_id = groups_KDS.group_id
          INNER JOIN T_POS_KDS kds ON groups_KDS.kds_id = kds.kds_id
          LEFT  JOIN T_POS_PRIORITY priority ON order_product.product_priority_id = priority.priority_id
      WHERE (iif(order_product.order_product_status_id != 4, ISNULL(order_product.send_to_kds, 0), ISNULL(order_product.send_to_kds_annulation, 0)) = 0
             OR (SELECT suborder_status_id FROM T_POS_SUBORDER WHERE suborder_id = order_product.suborder_id) = 3)
          AND order_product.suborder_id = @idSubOrder
          AND product.enableKDS = 1 AND ISNULL(product.kdsEnvironment, 0) = 1
          AND isnull(product.kdsStation, 0) = 0
          AND  order_product.order_product_status_id = iif(@changeActiveKdsXetux = 1, order_product.order_product_status_id, @idStatus)
          AND ((select count(product_id) from T_POS_KDS_GROUP_ENVIRONMENT_PRODUCT where kds_group_environment_id = KGEN.id) > 0
               AND order_product.product_id in(select product_id from T_POS_KDS_GROUP_ENVIRONMENT_PRODUCT where kds_group_environment_id = KGEN.id))

          UNION ALL

    -- PRODUCTOS POR TIPO DE ORDEN
        SELECT ISNULL(order_product.preparation_time, 1) preparation_time, product.product_id, item.item_name,
                    priority.priority_description as family_name, order_product.quantity,  kds.kds_code,
                    ISNULL(order_product.order_product_id_parent_annulation, 0) order_product_id_parent_annulation,  order_product.order_product_id,
                    @kds_group_id_orderType as kds_group_id,
                    order_product.seat_number,
          ISNULL(priority.priority_description, '') as priority,
          ISNULL(priority.catdelay, '') as catdelay,
          ISNULL(priority.priority_id, 1) as priority_id, isnull(priority.rgb_color,'#F7F7F7') as rgb_color
            FROM T_POS_ORDER_PRODUCT order_product
          INNER JOIN T_POS_PRODUCT product ON order_product.product_id = product.product_id
          INNER JOIN T_POS_ITEM item ON product.item_id = item.item_id
          INNER JOIN T_POS_SUBORDER so ON so.suborder_id = order_product.suborder_id
          INNER JOIN T_POS_ORDER O ON o.order_id = so.order_id
          INNER JOIN T_POS_PRODUCT_FAMILY product_family ON product.product_id = product_family.product_id AND product_family.is_family_default = 1
          INNER JOIN T_POS_FAMILY family ON product_family.family_id = family.family_id and family.family_type_id = 1
          INNER JOIN T_POS_KDS_GROUP groups_KDS ON groups_KDS.group_id = @kds_group_id_orderType
          INNER JOIN T_POS_KDS kds ON groups_KDS.kds_id = kds.kds_id
          LEFT  JOIN T_POS_PRIORITY priority ON order_product.product_priority_id = priority.priority_id
      WHERE (iif(order_product.order_product_status_id != 4, ISNULL(order_product.send_to_kds, 0), ISNULL(order_product.send_to_kds_annulation, 0)) = 0
             OR (SELECT suborder_status_id FROM T_POS_SUBORDER WHERE suborder_id = order_product.suborder_id) = 3)
          AND order_product.suborder_id = @idSubOrder
          AND product.enableKDS = 1 AND order_product.order_product_status_id = iif(@changeActiveKdsXetux = 1, order_product.order_product_status_id, @idStatus)
      ORDER BY  priority_id, order_product.order_product_id
    END TRY

    BEGIN CATCH
        -- Manejo de errores opcional
    END CATCH
END
GO