Category Archives: Banco de Dados

Modelos Prontos – Módulo de Orçamento em Excel VBA

Olha o pessoal do fórum aprontando de novo. Dessa vez foi o colega Leandro Alves o autor da proeza. Abaixo coloco o post do fórum referente ao excelente modelo de planilha disponibilizado pelo colega:

Olá Pessoal,

Estou disponibilizando este modelo de orçamento que fiz para o meu próprio uso. E modéstia a parte ficou legalzinho. Conforme a minha necessidade, não o fiz para realizar pesquisas num banco de dados porque colho as informações manualemte direto do site dos meus fornecedores.

Este é um aplicativo que calcula automaticamente o preço de venda de uma mercadoria baseado no seu preço de custo e na margem de lucro escolhida pelo usuario. Também pode-se estipular um preço de venda e ele calcula automaticamente a margem obtida. No rodapé ele mostra os dados financeiros do orçamento sendo eles Custo Total, Média da Margem de Lucro, Lucro por Produto e Lucro Total. Temos opções para desconto, acrescimo e ocultar informaçoes financeiras caso o cliente precise ver o orçamento.

É possivel Gravar, Editar, imprimir e Apagar um orçamento buscando-o através da tela de pesquisa onde o usuario realiza filtros por uma data especifica, por nome e até por intervalo de datas. E outro detalhe importante é que ao gerar um novo orçamento sempre é gerado num novo numerio sequencial para o mesmo automanticamente.

*Lembrando que todas as informações contidas no arquivo são ficticias e foram inseridas somente para testes.

Espero que gostem!

😀

Abraços

Imagens do Modelo

Orcamento - Tela de Cadastro

Orcamento - Tela de Pesquisa

Download da planilha

Orçamento.rar (92.06 KiB)

Tópico do Fórum

http://www.tomasvasquez.com.br/forum/viewtopic.php?f=23&t=1654

E um merecido agradecimento ao colega pela contribuição!

Há muitos outros colegas disponibilizando disversos modelos em nosso fórum. Vale a pena dar uma olhada!

Um grande abraço e bom proveito!

Update – Modelo de Cadastro em VBA no Excel

Olá pessoal,

Atendo a anseios de muitos que entraram em contato solicitando alterações desde seu lançamento, atualizei o Modelo de Aplicativo de Cadastro em VBA no Microsoft Excel para prever o que foi “mais pedido”.

Uma explicação completa e a nova versão podem ser acessadas nos links abaixo:

https://www.tomasvasquez.com.br/blog/modelo-de-aplicativo-de-cadastro-em-vba-no-microsoft-excel

Discussões sobre a planilha podem ser acompanhadas por aqui:

http://www.tomasvasquez.com.br/forum/viewforum.php?f=5

Bom proveito a todos

SQL – Gerando Inserts de Tabelas via T-SQL

Mais uma da série: me aventurando no SQL Server.

Apesar do SQL Management Studio Express ser uma ferramenta poderosa e gratuita, peca em algumas funcionalidades simples que a maioria das ferramentas free tem. A título de exemplo, as ferramentas do MySQL como o Administrator e o Query Browser são extremente completas, auxiliando inclusive em uma funcionalidade que coloque em questão aqui. A geração de scripts de dados de um banco.

Tá, conseguimos facilmente geraro script de criação das tabelas, procs, enfim, tudo mais de objetos existentes no banco através SQL Management Studio Express, clicando com o botão direto do mouse sobre o database na janela Object Explorer, indo em Tasks, Generate Scripts… Daí, basta selecionar os objetos a serem gerados e voilá! Está tudo aí, pronto para ser salvo e criado em qualquer outra servidor.

sqlmanagementstudioexpressgeneratescripts
Gerando Scripts do SQL Management Studio

O problema são os dados. Tudo bem, dá até pra fazer um backup em arquivo binário, mas e se o servidor for de acesso restrito? A rotina de backup só funciona apontando para os discos existentes no computador instalado. Com algum esforço, daria para apontar para um diretório da rede, mas nem sempre isso está acessível.

Portanto, para auxiliar nesta tarefa, o colega Mark Clerget publicou um seu blog uma query muito bacana que é capaz de gerar os inserts dos dados das tabelas de um determinado banco. Dê uma olhada no script abaixo:

CREATE TABLE #tmp (
SQLText		VARCHAR(8000) )
 
CREATE TABLE #tmp2 (
Id		INT IDENTITY,
SQLText		VARCHAR(8000) )
 
SET nocount ON
 
DELETE #tmp
DELETE #tmp2
 
DECLARE	@vsSQL		VARCHAR(8000),
@vsCols		VARCHAR(8000),
@vsTableName 	VARCHAR(40)
 
DECLARE csrTables cursor FOR
SELECT 	name
FROM 	sysobjects
WHERE 	TYPE IN ('u')
AND	name IN ('Customers')
ORDER BY name
 
OPEN csrTables
fetch NEXT FROM csrTables INTO @vsTableName
 
while (@@fetch_status = 0)
BEGIN
 
SELECT 	@vsSQL = '',
@vsCols = ''
SELECT	@vsSQL = @vsSQL +
CASE 	WHEN sc.type IN (39,47,61,111) THEN
'''''''''+' + 'isnull(rtrim(replace('+ sc.name + ','''''''','''''''''''')),'''')' + '+'''''',''+'
WHEN sc.type = 35 THEN
'''''''''+' + 'isnull(rtrim(replace(substring('+ sc.name + ',1,1000),'''''''','''''''''''')),'''')' + '+'''''',''+'
ELSE
'isnull(convert(varchar,' + sc.name + '),''null'')+'',''+'
END
FROM	syscolumns sc
WHERE	sc.id = object_id(@vsTableName)
ORDER BY ColID
 
SELECT	@vsCols = @vsCols + sc.name + ','
FROM	syscolumns sc
WHERE	sc.id = object_id(@vsTableName)
ORDER BY ColID
 
SELECT @vsSQL = SUBSTRING(@vsSQL,1,datalength(@vsSQL)-1)
 
SELECT @vsCols =  SUBSTRING(@vsCols,1,datalength(@vsCols)-1)
 
INSERT #tmp
EXEC ('select ' + @vsSQL + ' from ' + @vsTableName)
 
UPDATE #tmp
SET sqltext = 'insert ' + @vsTableName + '(' + @vsCols + ') values(' + SUBSTRING(sqltext,1,datalength(sqltext)-1) + ')'
 
INSERT #tmp2
SELECT 'DELETE from ' + @vsTableName
 
INSERT #tmp2 VALUES ('GO')
 
IF (SELECT COUNT(id) FROM syscolumns WHERE id = object_id(@vsTableName)  AND ((STATUS & 128) = 128) ) = 1
BEGIN
INSERT #tmp2
SELECT 'set identity_insert ' + @vsTableName + ' on'
END
 
INSERT #tmp2
SELECT * FROM #tmp
 
IF (SELECT COUNT(id) FROM syscolumns WHERE id = object_id(@vsTableName)  AND ((STATUS & 128) = 128) ) = 1
BEGIN
INSERT #tmp2
SELECT 'set identity_insert ' + @vsTableName + ' off'
END
 
INSERT #tmp2 VALUES ('GO')
 
INSERT #tmp2
SELECT 'update statistics ' + @vsTableName
 
INSERT #tmp2 VALUES ('GO')
 
DELETE #tmp
 
fetch NEXT FROM csrTables INTO @vsTableName
 
END
 
close      csrTables
deallocate csrTables
 
UPDATE #tmp2
SET sqltext = SUBSTRING(sqltext,1,charindex(',)',sqltext)-1) + ',NULL)'
WHERE NOT(charindex(',)',sqltext) = 0)
 
UPDATE #tmp2
SET sqltext = REPLACE(sqltext, ',''''',',null')
WHERE NOT (charindex(',''''',sqltext) = 0)
 
UPDATE #tmp2
SET sqltext = REPLACE(sqltext, '(''''',',null')
WHERE NOT (charindex('(''''',sqltext) = 0)
 
SET nocount off
 
SELECT sqltext FROM #tmp2 ORDER BY id
 
GO
 
DROP TABLE #tmp
DROP TABLE #tmp2

Atentem à linha com o código “and name in ('Customers')” pois é este que faz o filtro nas tabelas contidas em sysobjects.. Se quiser apenas um subconjunto das tabelas nas quais terão o script gerado, basta alterar este filtro. O resultado da query acima executada sobre o banco de dados Northwind na tabela Region, pode ser visto na figura abaixo:

Script Gerado
Script Gerado

É um belo macete para as horas de aperto. Porém, este script não é infalível. Para alguns tipos de campos, como o image, ainda são gerados alguns erros. Mas como eles são excessão, é possível tratá-los de maneira separada. Caso alguém tenha alguma sugestão de melhoria, é avisar!

Abraços

Tomás

SQL – Apagando todos os registros de um banco de dados SQL Server

Bom, os leitores devem ter percebido que ultimamente tenho me aventurando a escrever algumas coisas sobre banco de dados. As experiências que tive em um projeto recente me levaram a ter que descobrir alguns macetes do SQL Server, os quais tratarei de publicar aqui na medida do possível.

Uma das necessidades da última aplicação era a constante troca de ambiente. Uma hora desenvolvimento, outra produção, outra qualidade, enfim.

Como o acesso era extramente restrito, não era algo trivial ficar “dropando” a base de dados, até porque nem era possível. O jeito era trabalhar direto no SQL para substituir algumas rotinas que a ferramenta e o acesso em nível administrativo facilitavam.

Por exemplo, para facilmente excluir todos os dados de todas a tabelas de um banco de dados SQL Server, a seguinte query resolve o problema:

-- desativa a integridade referencial
EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
GO

EXEC sp_MSForEachTable '
IF OBJECTPROPERTY(object_id(''?''), ''TableHasForeignRef'') = 1
DELETE FROM ?
else
TRUNCATE TABLE ?
'
GO

-- reativa a integridade refencial
EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL'
GO

Em detalhes, o que a query faz: ela usa uma stored procedure não documentada do SQL Server chamada sp_MSForEachTable. Resumidamente, esta stored procedure dispara um comando SQL passado por parâmetro em todas as tabelas de um database, sendo o nome da tabela substituído pelo caracter “?”, como pode ser visto na query anterior. Se além de de apagar os dados das tabelas, quiser também reiniciar a contagem dos campos de chave primária, mais conhecido como RESEED, bastaria colocar a seguinte query antes da reativação da integridade referencial:

-- Reinicia o contador de chave primária de todas as tabelas
EXEC sp_MSForEachTable '
IF OBJECTPROPERTY(object_id(''?''), ''TableHasIdentity'') = 1
DBCC CHECKIDENT (''?'', RESEED, 0)
'
GO

Na falta de ferramentas ou privilégios, esse tipo de macete é uma mão na roda.

Referência:

http://blogs.officezealot.com/mauro/archive/2006/03/12/9402.aspx

Abraços

Tomás