Querys para monitoreo masivo en la múltiple SDS
--TIENDAS SIN APERTURAR
Select getdate() as 'HORA ACTUAL', lasttran,eopstatus,acctstatus from acctperiod where acctstatus = 'C' order by getdate() desc
--REVISIÓN DE TRANSACCIONES DE POSTEAR EN TIENDA
Select trandate,sum(case when pdbupd = 1 then 1 else 0 end) PDB1,sum(case when pdbupd = 0 then 1 else 0 end) PDB0 from tranfile (NoLock) Where trandate = convert(date,getdate()) Group By Trandate Having Count (*) > 10
SELECT COUNT(TranNumber) AS 'Transacciones Pendientes No Posteadas' FROM TranTotal WHERE Posted = 0 and trandate = convert(date,getdate()) Group By trandate Having Count (*) > 0
SELECT posted, registerId, trannumber, trandate, endtime, txntotal, rectype, operatmode, storenumber, typeofsale, employeeno FROM TranTotal t WHERE Posted = 0 and trandate = convert(date,getdate()) Order by t.Endtime desc
--SALTOS DE TRANSACCIONES--
SET NOCOUNT ON Declare @date date = convert(date,getdate()); Select * From (select Substring(@@servername,6,4) 'Tienda',Registerid, FirstMissingTran,'Primer' as 'TransFaltan',1 as'1' from (select RegisterId, TranNumber+1 as FirstMissingTran from TranTotal (NoLock) where TranDate = @Date except select RegisterId, TranNumber from TranTotal where TranDate = @Date) A WHERE FirstMissingTran <= (Select MAX(TranNumber) from TranTotal (NoLock) where RegisterId = A.RegisterID and TranDate = @Date) AND RegisterId !=80 Union All select Substring(@@servername,6,4) 'Tienda',Registerid, LastMissingTran as 'Transaccion','Ultima' as 'TransFaltan', 1 as'1' FROM( select RegisterId, TranNumber-1 as LastMissingTran from TranTotal (NoLock) where TranDate = @Date except select RegisterId, TranNumber from TranTotal NoLock where TranDate = @Date ) A WHERE LastMissingTran >= (Select MIN(TranNumber) from TranTotal (NoLock) where RegisterId = A.RegisterID and TranDate = @Date) AND RegisterId != 80) Tranes Order By Tienda asc,Registerid asc, FirstMissingTran asc
--TRANSACCIONES DELETE--
SELECT posted, registerId, trannumber, docnumber, trandate, endtime, txntotal, rectype, operatmode, storenumber, typeofsale, employeeno from trantotal where rectype = 'D' and trandate = convert(date,getdate())
/*
--Estatus de cierre de tiendas
DECLARE @Date datetime = '2023-06-04'
Select 'CERRADAS', status FROM STORE_FinalTenderTotals WHERE CAST(Date as date) = @Date order by status asc
--si hay 132 aprovadas la operacion de todas las tiendas ha finalizado.
--VALIDAR SI ALGUNA TIENDA NO HIZO SU FINAL PARA LA FECHA ESPECIFICADA
DECLARE @Date datetime = '2023-06-04'
IF EXISTS (SELECT 1 FROM STORE_FinalTenderTotals WHERE CAST(Date AS DATE) = @Date)
BEGIN SELECT 'Caja General Aprobada' AS Result END ELSE BEGIN SELECT 'Sin Corte' AS Result END
*/
--Select * from tranfile_sale_queue reiniciar datapush
SELECT COUNT(TranNumber) AS 'Registros Pendientes' from tranfile_sale_queue where Trandate = convert(date,getdate()) Group By Trandate Having Count (*) > 10
--Revisión de OutboundIntegrationTransactions, si hay registros reiniciar datapush
SELECT 'OutBound 002', COUNT(*)
FROM OutboundIntegrationTransactions WHERE WebServiceTypeCode = '002' AND Payload LIKE CONCAT('%<BeginDateTime>', FORMAT(GETDATE(), 'yyyy-MM-dd'), '%')
GROUP BY WebServiceTypeCode HAVING COUNT(*) > 10;--EN CASO DE QUE EN ALGUNA TIENDA NO SE VAYAN LOS REGISTROS
--Revisar si en el campo de ProcessingCount está en 3, si es así los registros ya no se van a ir
--entonces tendríamos que aplicar el update para colocar ProcessingCount en 0 y que se vuelvan a procesar
--SELECT 'OutBound 002', *
--FROM OutboundIntegrationTransactions WHERE WebServiceTypeCode = '002' AND Payload LIKE CONCAT('%<BeginDateTime>',
--FORMAT(GETDATE(), 'yyyy-MM-dd'), '%')
--update ProcessingCount a 0
--update OutboundIntegrationTransactions set ProcessingCount = 0 where WebServiceTypeCode = '002'
--AND Payload LIKE CONCAT('%<BeginDateTime>', FORMAT(GETDATE(), 'yyyy-MM-dd'), '%')--NO POSTEADAS CON SYSTEM DATE ERROENO.
Select 'Dia anterior', posted, registerId, trannumber, systemdate, trandate, endtime, txntotal, rectype, operatmode, storenumber, typeofsale, employeeno, docnumber From TranTotal (NoLock) where systemdate = convert(date,getdate()) and Trandate <> Systemdate order by StoreNumber
--select * from store_exchangerate where codetype = 'Purchase' and lastupdate = '2023-03-06'
--Alerta Mensajeria IF002--
--Select '>3' as'Contador>3', LastProcessingErrorMessage, * From OutboundIntegrationTransactions (NoLock) where WebServiceTypeCode = '002' and LastProcessingErrorMessage is not Null
---Versión de POS - Actual 2.1.0.12
/*
WITH CheckVersion as (SELECT Substring(@@servername,6,4) 'Store',t.registerid ,t.trannumber ,t.trandate , SUBSTRING(e.ej,CHARINDEX('TouchPos',EJ)+18,9) 'Version',row_number()
over (partition by t.registerid order by t.trannumber desc) as rn FROM ElectronicJournal e (Nolock) LEFT JOIN trantotal t (nolock) on
t.registerid = e.registerid and t.trannumber = e.trannumber and t.trandate = e.trandate WHERE e.TranDate = CONVERT(DATE,GETDATE()) and t.rectype = 'O')
Select * from CheckVersion where rn = 1 and [Version] <> '2.1.0.12'
*/
---MONITOREAR OutboundIntegrationTransactions
/*
DECLARE @Interface NVARCHAR(10) = '002'
DECLARE @TranDate DATE = '2023-06-04'
SELECT 'INTEGRATION', id, WebServiceSettingcode, webservicetypecode, typecode, payload, runsequence, lastprocessingtimestart FROM OutboundIntegrationTransactions
WHERE WebServiceTypeCode = @Interface
AND Payload LIKE CONCAT('%<BeginDateTime>', FORMAT(@TranDate,'yyyy-MM-dd'), '%')
ORDER BY DateCreated DESC
*/
--TRANES DE SCO CON EMPLEADO INCORRECTO
Select 'tranes en SCO', posted,trannumber, Endtime, systemdate, TranDate, registerid, rectype, employeeno from Trantotal t where systemdate = convert(date,getdate()) and t.registerid in (50, 51, 52, 53, 55) and t.employeeno != 9995 Order by t.endtime desc
--PARCIALES EN SCO
SELECT 'Parciales en SCO', WithdrawalId, date, PartialNumber, STATUS, DocumentType, Comment FROM STORE_Withdrawals WHERE Cast(Date as date) = convert(date,getdate()) and EmployeeNo = 9995 and documentType = 'partial'
