JMamede.com

Soluções para os seus negócios

Email: jmamede@jmamede.com




Index

REGRAS Teclas de Utilizador
- Controlo de Código Postal nacional (NNNN-NNN) - Controlo de sequência numérica e cronológica de faturação
- Controlo de Data/Hora de transporte (Dossiers internos) - Controlo de documentos de faturação em falta
- Controlo de Data/Hora de transporte (Documentos faturação)
Comandos TSQL Outros
- Criação de moradas de carga e descarga - Backups automáticos com SQLEXPRESS
- Correção de códigos postais
- Criação de códigos EAN13 e EAN14
- Listar clientes com NIPC/NIF inválido





Criar função de validação de NIPC/NIF em SQL

Vamos criar a função de validação de NIPC/NIF na base de dados para posterior utilização nas nossas querys. Se a função retornar 0 (zero) é porque o contrinuinte está errado.

 

 

CREATE FUNCTION [dbo].[nipc_nif_control]
(@ncont nvarchar(25))
RETURNS bit
AS
BEGIN
	DECLARE @checkdigit int, 
			@soma int, 
			@return bit

	set @ncont=ltrim(rtrim(@ncont))

	if left(@ncont, 1) not in ('1', '2', '5', '6', '7', '8', '9') or isnumeric(@ncont)=0
	return cast(0 as bit)

	set @soma= left(@ncont, 1)*9 +
	substring(@ncont, 2, 1)*8+
	substring(@ncont, 3, 1)*7+
	substring(@ncont, 4, 1)*6+
	substring(@ncont, 5, 1)*5+
	substring(@ncont, 6, 1)*4+
	substring(@ncont, 7, 1)*3+
	substring(@ncont, 8, 1)*2

	set @return=1
	set @checkdigit=0

	while @soma%11 <> 0
	begin
		set @checkdigit=@checkdigit+1
		set @soma=@soma+1
	end

	if @checkdigit=10
	set @checkdigit=0

	if @checkdigit<>substring(@ncont, 9, 1)
	set @return=0

	RETURN @return
END

 

 

Agora vamos filtrar os clientes com NIPC/NIF que seja inválido (apenas clientes nacionais com contribuinte com 9 dígitos todos numéricos).

 

 

select ncont,* from cl where pais=1 and len(ncont)=9 and ISNUMERIC(ncont)=1 and dbo.nipc_nif_control(ncont)=0






Gerar códigos EAN 13 e 14

Vamos supor que de repente tem 3000 ou 4000 que tem de gerar códigos EAN 13 ou EAN14. Para isso elaborei um pequeno script para de forma rápida gerar todos esses códigos.

 

 

 

-- Vamos popular o campo ST.CODIGO com os 12 dígitos para posterior cálculo do checkdigit
--  Para este preenchimento directo, vamos usar o campo identity do st para gerar o código de 12 dígitos
 
 update st set codigo='5601234'+right('00000'+rtrim(ltrim(str(stid))), 5)

-- Agora que temos o campo ST.CODIGO com o EAN13 sem o checkdigit, 
--  vamos atualizar o campo para colocar o checkdigit correto

update st set codigo= rtrim(ltrim(codigo)) + 
rtrim(ltrim(right(10-right(
substring(codigo,len(codigo),1)*3
+substring(codigo,len(codigo)-1,1)*1
+substring(codigo,len(codigo)-2,1)*3
+substring(codigo,len(codigo)-3,1)*1
+substring(codigo,len(codigo)-4,1)*3
+substring(codigo,len(codigo)-5,1)*1
+substring(codigo,len(codigo)-6,1)*3
+substring(codigo,len(codigo)-7,1)*1
+substring(codigo,len(codigo)-8,1)*3
+substring(codigo,len(codigo)-9,1)*1
+substring(codigo,len(codigo)-10,1)*3
+substring(codigo,len(codigo)-11,1)*1
+substring(codigo,len(codigo)-12,1)*3
,1),1))) from st where len(codigo)=12


-- Como queremos paralelamente usar o EAN14, vamos colocar em BC (códigos de barras alternativos)
-- o EAN14, que é calculado com base no EAN13

insert into bc (bcstamp, ststamp, ref, design, codigo)
select left(newid(), 23), ststamp, ref, design, rtrim(ltrim(codigo)) + 
rtrim(ltrim(right(10-right(
substring(codigo,len(codigo),1)*3
+substring(codigo,len(codigo)-1,1)*1
+substring(codigo,len(codigo)-2,1)*3
+substring(codigo,len(codigo)-3,1)*1
+substring(codigo,len(codigo)-4,1)*3
+substring(codigo,len(codigo)-5,1)*1
+substring(codigo,len(codigo)-6,1)*3
+substring(codigo,len(codigo)-7,1)*1
+substring(codigo,len(codigo)-8,1)*3
+substring(codigo,len(codigo)-9,1)*1
+substring(codigo,len(codigo)-10,1)*3
+substring(codigo,len(codigo)-11,1)*1
+substring(codigo,len(codigo)-12,1)*3
,1),1))) from st where len(codigo)=13

Já temos o ST.CODIGO om o EAN13 e na tabela BC o EAN14 correspondente, .... para todos os artigos.






Controlo de Data e Hora de transporte (Dossiers).

Tem de ser pelo menos para uma data/hora superior à data/hora atual (pelo menos um minuto suprior à data/hora do sistema).

 

 

if sbo.adding=.t.
	select bo2
	if bo2.tiposaft <> '--'
		if bo2.xpddata < date()
			messagebox('A data de transporte é anterior à data atual!')
			return .f.
		endif

		if bo2.xpddata = date() and bo2.xpdhora <= time()
			messagebox('A hora de transporte é anterior ou igual à hora atual!'+chr(13)
				+'Tem de ter no mínimo mais 1 minuto que a hora atual.')
			return .f.
		endif
	else
		return .t.
	endif
endif
return .t.





Controlo de Data e Hora de transporte (Faturação).

Tem de ser pelo menos mais 10 minutos superior à data/hora atual.

 

 

if sft.adding=.t.
    select ft
    if ft.ndoc>0
        if ft.cdata < date()
            messagebox('A data de transporte é anterior à data atual!')
            return .f.
        endif

        LM=right('0000'+alltrim(ft.chora), 2)
        if val(LM)>59
            messagebox('Hora de transporte inválido!')
            return .f.
        endif

        LH=left(right('0000'+alltrim(ft.chora),4), 2)
        if val(LH)>23
            messagebox('Hora de transporte inválido!')
            return .f.
        endif

        LHM=LH+LM

        u_sqlexec([select replace(convert(char(5),dateadd(mi,10,getdate()), 114),':','') as sqltime],[sqltime])
        select sqltime
        LVCSQLTIME=sqltime.sqltime

        if ft.cdata = date() and LHM <= LVCSQLTIME
            messagebox('A hora de transporte é anterior ou igual à hora atual!'+chr(13)
				+'Tem de ter no mínimo mais 10 minutos que a hora atual.')
            return .f.
        endif
    else
        return .t.
    endif
endif
return .t.





Controlo de sequência numérica e cronológica dos documento de faturação

Não sendo esta uma situação normal, nada nos impede que por alguma razão uma série de documentos não tenha falhas de sequencia numérica e cronológica num ou outro documento, especialmente se estamos a importar documentos de outras fontes.

Para validar essas falhas, sugiro uma tecla de utilizador associado ao ecran de faturação (bastando mudar o documento na combo box do form para ir fazendo a validação por tipo de documento), com o seguinte código:

 

 

lvndoc=y_ndoc
lvnano=getnome('Qual o ano dos documentos?',year(date()),year(date()))

u_sqlexec([select fno, fdata, cast(0 as bit) as marcada from ft where ndoc=]+str(lvndoc)
	+[ and ftano=]+str(lvnano)+[ order by fno],[Listdatas])

select Listdatas
scan
	if recno()=1
		lvnnum=Listdatas.fno
		lvdanterior=Listdatas.fdata
	else
		if Listdatas.fdata < lvdanterior
			replace Listdatas.marcada with .t.
		endif

		lvdanterior = Listdatas.fdata
	endif
endscan

select Listdatas

delete for Listdatas.marcada=.f.

select Listdatas
mostrameisto('Listdatas')

return .t.





Controlo de números de documentos de faturação em falta

Não sendo esta uma situação normal, nada nos impede que por alguma razão uma série de documentos não tenha falta um ou outro documento, especialmente se estamos a importar documentos de outras bases de dados.

Para validar essas falhas, sugiro uma tecla de utilizador associado ao ecran de faturação (bastando mudar o documento na combo box do form para ir fazendo a validação por tipo de documento), com o seguinte código:

 

 

create cursor numeros (numeros N (12,0))

lvndoc=y_ndoc
lvnano=getnome('Qual o ano dos documentos?',year(date()),year(date()))

u_sqlexec([select min(fno) as min, max(fno) as max from ft where ndoc=]+str(lvndoc)
	+[ and ftano=]+str(lvnano),[NumeradoresFT])

select NumeradoresFT

lvnmin=NumeradoresFT.min
lvnmax=NumeradoresFT.max

do while lvnmin < lvnmax

	wait window ('Verificar registo » '+alltrim(str(lvnmin))+' de um total de '+alltrim(str(lvnmax))) nowait

	u_sqlexec([select count(*) as conta from ft where ndoc=]+str(lvndoc)+[ and fno=]+str(lvnmin)
		+[ and ftano=]+str(lvnano),[Contagem])
	select contagem
	if contagem.conta=0
		select numeros
		append blank
		replace numeros.numeros with lvnmin 
	endif

	lvnmin=lvnmin+1

enddo

select numeros
mostrameisto("numeros")
fecha("numeros")





Controlo de Códigos Postais (NNNN-NNN)

Regra para tabela de clientes

select CL
if cl.pais=1
	For x=1 To 8 Step 1
		if x=5
			if substr(cl.codpost, 5, 1)<>'-' 
				return .f.
			endif
		else
			if  not inlist(substr(cl.codpost, x, 1), '0', '1','2','3','4','5','6','7','8','9')
				return .f.
			endif
		endif
	Next
endif
return .t.
É um programa
Menssagem: O Código postal não obdece á normalização de códigos postais de Portugal (NNNN-NNN)

 

Regra para a faturação

select FT
if FT.pais=1
	For x=1 To 8 Step 1
		if x=5
			if substr(ft.codpost, 5, 1)<>'-' 
				return .f.
			endif
		else
			if  not inlist(substr(ft.codpost, x, 1), '0', '1','2','3','4','5','6','7','8','9')
				return .f.
			endif
		endif
	Next
endif
return .t.
É um programa
Menssagem: O Código postal não obdece á normalização de códigos postais de Portugal (NNNN-NNN)





Acerto dos códigos postais nas fichas de clientes

Listar clientes nacionais com códigos postais não conformes

select codpost, no, nome, morada, local, pncont, * from cl 
where (SUBSTRING(codpost, 5, 1)<>'-'
or SUBSTRING(codpost, 1, 1) not between '0' and '9'
or SUBSTRING(codpost, 2, 1) not between '0' and '9'
or SUBSTRING(codpost, 3, 1) not between '0' and '9'
or SUBSTRING(codpost, 4, 1) not between '0' and '9'
or SUBSTRING(codpost, 6, 1) not between '0' and '9'
or SUBSTRING(codpost, 7, 1) not between '0' and '9'
or SUBSTRING(codpost, 8, 1) not between '0' and '9')
 and pais=1

 

Limpar duplos espaços nos códigos postais (pode ser necessário correr mais que uma vez)

update cl set codpost=replace(codpost, '  ', ' ')

 

Códigos postais com o ifen mal posicionado ou com espaços antes ou depois

update cl set codpost=replace(codpost, ' - ', '-')
update cl set codpost=replace(codpost, '--', '-')  
update cl set codpost=replace(codpost, ' -', '-')

 

Códigos postais com o ifen mal posicionado , estando na posição 6 em vez de ser na 5

select codpost, * from cl where SUBSTRING(codpost, 6, 1)='-' and pais=1

  

update cl set codpost=left(codpost, 4)+'-'+ltrim(substring(codpost, 7, 40))  
where SUBSTRING(codpost, 6, 1)='-' and pais=1

 

Códigos postais apenas com o formato NNNN (Antigo formato sem arruamentos)

select codpost, substring (codpost, 6, 1), * from cl where SUBSTRING(codpost, 5, 1)=' ' and pais=1 
and left(codpost, 4) between '0000' and '9999' 
and substring (codpost, 6, 1)>'9'

  

update cl set codpost=left(codpost, 4)+'-'+'001'+substring(codpost, 5, 39) 
where SUBSTRING(codpost, 5, 1)=' ' and pais=1 and left(codpost, 4) between '0000' and '9999' 
and substring (codpost, 6, 1)>'9'

 

Códigos postais com o formato NNNN NNN (falta o ifen)

select codpost, * from cl where SUBSTRING(codpost, 5, 1)=' ' and pais=1
and left(codpost, 4) between '0000' and '9999' 
and substring (codpost, 6, 3) between '000' and '999'

  

update cl set codpost=left(codpost, 4)+'-'+substring(codpost, 6, 39) 
where SUBSTRING(codpost, 5, 1)=' ' and pais=1 
and left(codpost, 4) between '0000' and '9999' and substring (codpost, 6, 3) between '000' and '999'

 

clientes sem código postal

update cl set codpost='0000-000 '+rtrim(ltrim(codpost)) where 
(codpost='' or left(codpost, 1) not in ('0','1','2','3','4','5','6','7','8','9')) and pais=1






Criação de moradas de carga de descarga na versão 2014 a partir das fichas de cliente (TSQL)

Atualização de campo localidade na ficha de clientes (para os clientes sem localidade e com código postal com formato XXXX-XXX)

update cl set local=ltrim(substring(codpost,9,50)) where clstamp in (
select clstamp from cl where pais=1 and local='' and left(codpost, 4)
between '0000' and '9999'
and substring(codpost,  6,3) between '000' and '999')

Preencher dados na tabela de moradas de entrega

insert into szadrs (szadrsstamp, szadrsdesc, morada, local, codpost,
codpais, pais, no, estab, origem, nome)
select left(newid(), 23), left(nome+' '+ltrim(rtrim(str(no))), 60),
morada, local, codpost, 'PT', 'Portugal',
no, estab, 'CL', nome from cl where pais=1 and estab=0 and no not in
(select no from szadrs where origem='CL')

Criação de moradas de carga e descarga a partir das fichas de fornecedores (TSQL)

Atualização de campo localidade na ficha de fornecedores (para os fornecedores sem localidade e com código postal com formato XXXX-XXX)

update fl set local=ltrim(substring(codpost,9,50)) where flstamp in (
select flstamp from fl where pais=1 and local='' and left(codpost, 4)
between '0000' and '9999'
and substring(codpost,  6,3) between '000' and '999')

Preencher dados na tabela de moradas de entrega

insert into szadrs (szadrsstamp, szadrsdesc, morada, local, codpost,
codpais, pais, no, estab, origem, nome)
select left(newid(), 23), left(rtrim(ltrim(nome))+'_F_'+ltrim(rtrim(str(no))), 60),
morada, local, codpost, 'PT', 'Portugal',
no, estab, 'FL', nome from fl where pais=1 and estab=0 and no not in
(select no from szadrs where origem='FL')





Usar Terefas Agendadas do Windows para fazer backups do SQL

Façam download do ficheiro

Plano de Backups

Para garantirem uma série de backups, recomendo que por cada dia da semana criem um ficheiro BAT diferente, com o neme da cópia de segurança diferente.
Assim terão 1 backup por dia da semana, noo caso de ser detetado alguma anomalia na base de dados, poderão reverter até 1 semana para trás.
O código que consta do ficheiro é o seguinte:

 

@ECHO OFF

 

REM **** OSQL PARA SQL INFERIOR A SQL2005 ********
REM osql -E -S servidor\sqlexpress -d maisritmo -Q "BACKUP DATABASE [maisritmo] TO DISK = N'c:\backupphc\maisritmo_impar.bak' WITH INIT , NOUNLOAD , NAME = N'maisritmo backup', NOSKIP , STATS = 10, NOFORMAT "
REM osql -E -S servidor\sqlexpress -d maisritmo -Q "update para1 set valor=convert(char(10), getdate(), 104) where descricao='ge_dt_Bkp'"

 

REM **** USAR SQLCMD PARA SQL2005 OU POSTERIOR ****
sqlcmd -E -S servidor\SQLEXPRESS -d BDPHC -Q "BACKUP DATABASE [maisritmo] TO DISK = N'c:\backupphc\maisritmo_impar.bak' WITH INIT , NOUNLOAD , NAME = N'maisritmo backup', NOSKIP , STATS = 10, NOFORMAT "
sqlcmd -E -S servidor\SQLEXPRESS -d BDPHC -Q "update para1 set valor=convert(char(10), getdate(), 104) where descricao='ge_dt_Bkp'"

 

REM **** CASO TENHAM O 7ZIP, PODEM COMPACTAR E FAZER CÓPIA DO BACKUP PARA UNIDADE EXTERNA
REM 7z a -tzip c:\backupphc\maisritmo_impar.zip c:\backupphc\maisritmo_impar.bak
REM DEL "c:\backupphc\maisritmo_impar.bak"
REM COPY "c:\backupphc\maisritmo_impar.zip" "E:\backupphc\maisritmo_impar.zip"
 

 

 

Dados a alterar:
Nome do SERVIDOR: servidor\sqlexpress

Nome da Base dados: maisritmo

e respetivas diretorias de ficheiross