ihUgfMFQ

1-- =====================================================================================
2-- Create Table dbo.bio_Utilizadores =================================================
3-- Autor: Nelson Ventura ==============================================================
4-- Data: 2021.04.18 ====================================================================
5-- =====================================================================================
6IF NOT EXISTS (SELECT 1 FROM sys.objects WHERE name ='bio_Utilizadores' AND Type ='U')
7BEGIN
8	CREATE TABLE bio_Utilizadores
9	(
10		
11		Id INT IDENTITY(1,1) NOT NULL,
12		Utilizador NVARCHAR (20) unique NOT NULL,
13		Nome NVARCHAR(50), 
14		Email NVARCHAR(50), 
15		Telefone NVARCHAR (20),
16		Password NVARCHAR(255) NOT NULL
17		CONSTRAINT 		PK_bio_Utilizadores						PRIMARY KEY(Id)	,
18		
19	)
20END
21ELSE
22BEGIN
23	PRINT 'bio_Utilizadores - Já existe'
24	--SELECT * FROM bio_Utilizadores
25	--DROP TABLE bio_Utilizadores
26END
27GO
28
29insert into bio_Utilizadores values ('HAALZ', 'Haalz Consultoria e Serviços', 'admin@haalz.com', '946736142', '82V3G6MW6FjrewnF2N5kqw==')
30
31
32
33 create procedure [dbo].[InsertAssiduidade] @codigofuncionario nvarchar(150) = null, @datainicio nvarchar(20)= null, @datafim nvarchar(20)= null
34as
35
36declare @inicio date
37set @inicio = @datainicio
38
39DECLARE @SessionsPerArea TABLE (codigofuncionario nvarchar(150), nomefuncionario nvarchar(50), startDate DATEtime,tempoEntrada nvarchar(50),tempoSaida nvarchar(50))
40INSERT  @SessionsPerArea 
41select pe.CodigoFuncionario,pe.NomeFuncionario, 
42convert(datetime, pe.data, 105),pe.Tempo as tempoEntrada,ps.Tempo as tempoSaida 
43from bio_Picagens pe inner join bio_Funcionarios f on f.ID = pe.IdFuncionario 
44inner join bio_PicagensSaida ps on f.ID = ps.IdFuncionario where pe.data 
45BETWEEN @datainicio AND @datafim and pe.codigofuncionario = @codigofuncionario
46
47declare @date datetime
48declare @codfunc nvarchar(100)
49
50set @codfunc = @codigofuncionario
51set @date = CONVERT(VARCHAR(50), @inicio, 105);
52
53with DaysInMonth as (
54   select @date as Date
55   union all
56   select dateadd(dd,1,Date)
57   from DaysInMonth
58   where month(date) = month(@Date))
59
60 insert into bio_Presenca select CAST(CONVERT(VARCHAR(50), Date, 121) AS DATE) as Data,  @codfunc ,t2.nomefuncionario, CAST(CONVERT(VARCHAR(50), t2.startDate, 121) AS DATE) as datapresente ,ISNULL(NULLIF(ltrim(rtrim((startDate))), ''), 'Falta') as presenca, t2.tempoEntrada,t2.tempoSaida from DaysInMonth t1 left join @SessionsPerArea t2 on t1.Date= startDate  where month(date) = month(@Date)
61GO
62