-- drop table #cur10
SELECT DISTINCT
CONTROL_SISTEMA, TITULO, A.COD_TRANSACAO, A.DATA_PARA_TRANSFERENCIA
into #cur10
FROM
TRANSACOES A
INNER JOIN
TRANSACOES_NAVEGA B ON A.COD_TRANSACAO =B.COD_TRANSACAO
WHERE
LEN(RTRIM(A.CONTROL_SISTEMA))>6 --AND SUBSTRING(A.CONTROL_SISTEMA,7,3)<>'SPK'
AND B.HABILITADO=1
ORDER BY
CONTROL_SISTEMA
SELECT DISTINCT
CONTROL_SISTEMA, TITULO, A.COD_TRANSACAO, A.DATA_PARA_TRANSFERENCIA
into #cur68
FROM
SERVIDOR.linx_homolog.dbo.TRANSACOES A
INNER JOIN
SERVIDOR..linx_homolog.dbo.TRANSACOES_NAVEGA B ON A.COD_TRANSACAO =B.COD_TRANSACAO
WHERE
B.HABILITADO=1
ORDER BY
CONTROL_SISTEMA
--COMPARA
SELECT A.CONTROL_SISTEMA AS CONTROL_SISTEMA_10,B.CONTROL_SISTEMA AS CONTROL_SISTEMA_68, A.TITULO FROM #CUR10 A
INNER JOIN #CUR68 B ON SUBSTRING(RTRIM(A.CONTROL_SISTEMA),1,6)=SUBSTRING(RTRIM(B.CONTROL_SISTEMA),1,6) --AND A.COD_TRANSACAO = B.COD_TRANSACAO
WHERE A.COD_TRANSACAO <> B.COD_TRANSACAO --A.CONTROL_SISTEMA<>B.CONTROL_SISTEMA --AND SUBSTRING(RTRIM(A.CONTROL_SISTEMA),1,1)<>'9'
ORDER BY A.CONTROL_SISTEMA
--
SELECT A.CONTROL_SISTEMA
AS CONTROL_SISTEMA_10,B.CONTROL_SISTEMA AS CONTROL_SISTEMA_68, A.TITULO FROM #CUR10 A
INNER JOIN #CUR68 B ON SUBSTRING(RTRIM(A.CONTROL_SISTEMA),1,6)=SUBSTRING(RTRIM(B.CONTROL_SISTEMA),1,6) AND A.COD_TRANSACAO = B.COD_TRANSACAO
WHERE
A.CONTROL_SISTEMA<>B.CONTROL_SISTEMA AND SUBSTRING(RTRIM(A.CONTROL_SISTEMA),1,1)<>'9'
ORDER BY A.CONTROL_SISTEMA
select * from #cur10
CREATE TABLE TT_DIRFILEORIGIN (ID INT IDENTITY ,NAME_FILE VARCHAR(100), TAMANHO BIGINT, DATA_CRIACAO DATETIME, DATA_ALTERACAO DATETIME)
CREATE TABLE TT_DIRFILEDESTIN (ID INT IDENTITY ,NAME_FILE VARCHAR(100), TAMANHO BIGINT, DATA_CRIACAO DATETIME, DATA_ALTERACAO DATETIME)
INSERT INTO TT_DIRFILEORIGIN (NAME_FILE , TAMANHO , DATA_CRIACAO , DATA_ALTERACAO ) VALUES('lx999901.SCT','7713','13/01/2022 10:18:28','02/08/2019 14:49:16')
SELECT * FROM TT_DIRFILEORIGIN (NOLOCK)
where NAME_FILE like 'lx%' and NAME_FILE not like '%spk%'
order by DATA_ALTERACAO
SELECT * FROM TT_DIRFILEDESTIN (NOLOCK)
where NAME_FILE like 'lx%' and NAME_FILE not like '%spk%'
order by DATA_ALTERACAO
truncate table TT_DIRFILEDESTIN
select * from #cur10
-- ARQUIVOS E TRANSACOES QUE TEM NO 10 E NÃO TEM NO 68
SELECT a.*
--INTO TMP_ATUALIZA_ARQ2022
FROM TT_DIRFILEORIGIN a
left join TT_DIRFILEDESTIN b on a.NAME_FILE=b.NAME_FILE
left join #cur10 c on substring(a.NAME_FILE,3,9) = c.CONTROL_SISTEMA
where a.NAME_FILE like 'lx%' --and a.NAME_FILE like '%scx%'
and b.NAME_FILE is null and CONTROL_SISTEMA is not null AND a.NAME_FILE NOT LIKE '%COPY%'
SELECT a.* FROM #cur10 a
left join TT_DIRFILEDESTIN b on c.NAME_FILE=b.NAME_FILE
left join TT_DIRFILEORIGIN c on substring(c.NAME_FILE,3,9) = a.CONTROL_SISTEMA
where NAME_FILE is null
--c.NAME_FILE like 'lx%' and c.NAME_FILE like '%scx%' and
--and b.NAME_FILE is null and CONTROL_SISTEMA is not null
SELECT a.*
INTO TMP_ATUALIZA_ARQ2022
FROM TT_DIRFILEORIGIN a
--left join TT_DIRFILEDESTIN b on a.NAME_FILE=b.NAME_FILE
left join #cur10 c on substring(a.NAME_FILE,3,9) = c.CONTROL_SISTEMA
where a.NAME_FILE like 'lx%' --and a.NAME_FILE like '%scx%'
AND c.CONTROL_SISTEMA IS NULL
----------------------------------------
******************** VARRE O DIRETÓRIO SELECIONADO E ARMAZENA EM TABELA
fso=createobject("scripting.filesystemobject")
fld=fso.getfolder(GETDIR())
f_wait("Aguatde...")
for each fil in fld.files
um = fil.name
dois = ALLTRIM(STR(fil.size))
tres = TTOC(fil.DateCreated)
quatro = TTOC(fil.DateLastModified)
cinsert = "INSERT INTO TT_DIRFILEORIGIN (NAME_FILE , TAMANHO , DATA_CRIACAO , DATA_ALTERACAO ) VALUES('" +um +"','"+dois+"','"+tres+"','"+quatro+"')"
f_insert(cinsert )
NEXT
f_wait()
**********************VARRE A TABELA ONDE CONSTA O NOME DO ARQUIVO, MOVE PARA OUTRO DIRETÓRIO E DELETA
f_select( "select * from TMP_ATUALIZA_ARQ2022","cur_2022")
SELECT cur_2022
*GO TOP
*BROWSE NORMAL
SCAN
*SET STEP ON
IF FILE("E:\Linx_sql_8\Linx\Exclusivos\"+ALLTRIM(cur_2022.name_file))
COPY FILE "E:\Linx_sql_8\Linx\Exclusivos\"+ALLTRIM(cur_2022.name_file) TO "C:\temp\ATUALIZACAO_LINX_2022\"+ALLTRIM(cur_2022.name_file)
DELETE FILE "E:\Linx_sql_8\Linx\Exclusivos\"+ALLTRIM(cur_2022.name_file)
ENDIF
ENDSCAN
