-- status de Procesamiento de las tablas Inbound e Import
/*
ID	Description
-- 0	Batch incomplete
 1	Pending
 2	In Process
 3	Processed
 4	Error
*/

/*Contiene el catalogo de las tablas que bajan informacion del gateway de MMS a SMS*/
SELECT *
    FROM thd_sms_gateway.dbo.Entity
	WITH (NOLOCK)

/*Guarda el registro de cada entidad y sus contadores que se realiza cada 5 minutos*/
SELECT TOP 500 *
    FROM IndexMessages
WITH (NOLOCK)
    WHERE StartDate >= '2024-02-16'
ORDER BY StartDate desc

--Consulta mensajes pendientes por procesar por nombre de entidad
select top 150 'InboundMessages', b.CreatedDateTime,b.MessageID,b.StatusID,b.RequestedUri from Messages  a  inner join InboundMessages  b on a.MessageID = b.MessageID 

where a.MMSTableName = 'ImportHMTransferHeader' order  by a.CreatedDateTime  desc 

/*Revisa con la siguiente consulta que el gateway esta sincronizacion con fecha y hora actual de la tienda (LastSyncDate)*/
select top 1 * from thd_sms_gateway.dbo.MessagesControlSync nolock order by LastSyncDate desc


/*Revisa embarques pendientes por procesarse*/
USE  thd_sms_gateway
GO
SELECT distinct				   com.StatusID,
							   com.MerchantShipmentNumber, 
							   com.Status,
							   com.TransferNumber,
							   com.CreatedOn,
							   com.CreatedById,
							   com.ShipDate,
							   com.ShipTime,
							   com.StoreDest,
							   com.ContainedID,
							   com.Seal,
							   com.IdCarrier,
							   com.ShipComments,
							   com.PalletOut,
							   com.ShipExcel,
							   com.AppointmentDate,
							   com.AppointmentTime,
							   com.Device,
							   com.AuditStatus,
							   com.ShipmentTypeId							  
						FROM Import.ComplementaryShipmentHeader com 
						WHERE --MerchantShipmentNumber  IN (8799219499,8799219594,8061217182) AND 
							 Deleted = 0  AND 
							 com.StatusID   <>3
							 
/*Revisar error en la tabla InboundError relacionado al embarque */							 
Select * from thd_sms_gateway.dbo.InboundError with (nolock) where ErrorDate >= '2024-02-02' 
AND ErrorMessage like '%1234567890%'
order by ErrorDate  DESC
							 
/*SI existe un embarque que se encuentra en estus 4 y no coresponde a la tienda lo actualizamos a estatu 3
para que deje procesar el rsto de los embaques*/

UPDATE Import.ComplementaryShipmentHeader 
SET StatusID = 3
WHERE MerchantShipmentNumber  IN () --Numero de embarque

/*SI existe un embarque se encuentra en estus 4 por que marco algun error en InboundError de la BD del gateway
pero ha hicimos, solicitamos la correccion debemos cambiarlo a estatus 1 para que se reprocese*/

UPDATE Import.ComplementaryShipmentHeader 
SET StatusID = 3
WHERE MerchantShipmentNumber  IN () --Numero de embarque



--Depura Transferencias de embarque,cuando baja alguna transferencia duplicada
declare @ship nvarchar(11) = 8799279247
DELETE FROM thd_sms_gateway.Import.Dlxferdet
WHERE Merchanttransferno IN (SELECT
    merchanttransferno
  FROM thd_sms_gateway.import.DlxFerHdr
  WHERE merchanttransferno IN (SELECT
    merchanttransferno
  FROM thd_sms_gateway.import.ShipmentTransfer
  WHERE merchantShipmentNumber = @ship))
  
 /*Delete a tablas de embarques en  gateway*/

declare @MerchantShipmentNumber varchar (10) =  8061220811
--delete  from thd_sms_gateway.import.ComplementaryShipmentHeader where MerchantShipmentNumber = @MerchantShipmentNumber 
--delete  from thd_sms_gateway.import.ShipmentHeader  where ShipmentNumber = @MerchantShipmentNumber 
--delete  from thd_sms_gateway.import.ShipmentDetail  where ShipmentNumber = @MerchantShipmentNumber 
--delete  from thd_sms_gateway.import.DlxFerHdr where ShipmentNumber = @MerchantShipmentNumber
 
----(Select D.* from thd_sms_gateway.import.DlxFerDet D 
----inner join thd_sms_gateway.import.DlxFerHdr H on D.MerchantTransferNo = H.MerchantTransferNo 
----where H.ShipmentNumber = @MerchantShipmentNumber )

--delete from thd_sms_gateway.import.DlxFerDet where MerchantTransferNo IN (Select MerchantTransferNo from thd_sms_gateway.import.DlxFerHdr where ShipmentNumber = @MerchantShipmentNumber )
--delete from thd_sms_gateway.import.DlxFerHdr where ShipmentNumber = @MerchantShipmentNumber

--delete from thd_sms_gateway.import.ShipmentReceive where MerchantShipmentNumber = @MerchantShipmentNumber 
--delete from thd_sms_gateway.import.ShipmentTransfer where MerchantShipmentNumber = @MerchantShipmentNumber 
--delete from thd_sms_gateway.import.RCPartialPO where MerchantShipmentNumber = @MerchantShipmentNumber 
--delete from thd_sms_gateway.import.HMPalletAudit where ShipNumber = @MerchantShipmentNumber 
--delete from thd_sms_gateway.import.HMPalletScan where MerchantShipNumber = @MerchantShipmentNumber 



/*Obtiene skus faltantes en  Itemreplen por embarque*/
declare @shipmentNumber NVARCHAR(20) = 8799269964
declare @tienda   varchar(12) 


			SELECT d.SKU As SKU
  FROM Import.DlxFerDet d INNER JOIN Import.DlxFerHdr h  ON h.MerchantTransferNo = d.MerchantTransferNo
 WHERE h.ShipmentNumber =  @shipmentNumber  AND   NOT EXISTS (SELECT NULL
                     FROM thd_sms_1176.[dbo].ItemReplen t2
                    WHERE t2.SKU = d.sku)
					
					
					

					
/*Revisar si existen mensajes pendientes por procesar para las tablas de los embarques  PAJ*/
SELECT
  a.MMSTableName
 ,b.*
FROM Messages a
INNER JOIN InboundMessages b
  ON a.MessageID = b.MessageID
WHERE b.CreatedDateTime >= DATEADD(DAY, -20, GETDATE())
AND a.MMSTableName IN ('Importrcpartialpo',
'importshipmentheader',
'importpalletheader',
'importhmpalletscan',
'Importshipmentreceive',
'Importshipmenttransfer',
'importdlxferhdr',
'importdlxferdet')
AND b.StatusID = 2

/*Reprocesar mensajes pendientes en gateway*/

--UPDATE b
--SET
--    StatusID = 1,
--    ProcessID = NULL,
--    ProcessCount = 1
--FROM
--    Messages a
--    INNER JOIN InboundMessages b ON a.MessageID = b.MessageID
--WHERE
--    b.CreatedDateTime >= DATEADD(DAY, -20, GETDATE())
--    AND a.MMSTableName IN ('Importrcpartialpo','importshipmentheader',
--        'importpalletheader','importhmpalletscan','Importshipmentreceive',
--        'Importshipmenttransfer','importdlxferhdr','importdlxferdet'
--    )
--    AND b.StatusID = 2





DECLARE	@shipmentNumber NVARCHAR(20) = 8799013923
Select  hmp.VP ,*  from thd_sms_gateway.Import.HMPalletScan hmp with (nolock)  where hmp.MerchantShipNumber = @shipmentNumber
--AND hmp.VP = 11000
order by  hmp.VP desc

--DELETE hmp from thd_sms_gateway.Import.HMPalletScan hmp   where hmp.MerchantShipNumber = @shipmentNumber
--AND hmp.VP = 11000

update Import.ComplementaryShipmentHeader SET StatusID = 1 
						WHERE MerchantShipmentNumber  IN (8799013923) AND 
							 Deleted = 0  AND 
							 StatusID   <>3