-- =============================================
-- Author:		M.Teresa Falomí
-- Create date: Desembre 2018
-- Description:	Inicialització de la base de dades
-- =============================================


USE dbInventari;
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


--Elimimen les dades de les taules
DELETE FROM Auditoria;
DELETE FROM UsuGestio;
DELETE FROM EntMaterial;
DELETE FROM LinEntMat;
DELETE FROM Ordinador;
DELETE FROM BoqSwitch;
DELETE FROM Referencia;
DELETE FROM FamReferencia;
DELETE FROM Usuari;
DELETE FROM TipusUsuari;
DELETE FROM Departament;
DELETE FROM Finestreta;
DELETE FROM Planta;
DELETE FROM Edifici;
DELETE FROM Proveidor;
DELETE FROM Ubicacio;
DELETE FROM HistAssigProg;
DELETE FROM HistAssigUsu;
DELETE FROM HistBus;
DELETE FROM HistPuntTreb;
DELETE FROM HistUsu;


--Inicialitzem els comptadors autonumèric
DBCC CHECKIDENT (Auditoria, RESEED,0);
DBCC CHECKIDENT (HistPuntTreb, RESEED,0);
DBCC CHECKIDENT (HistUsu, RESEED,0);
DBCC CHECKIDENT (LinEntMat, RESEED,0);
DBCC CHECKIDENT (EntMaterial, RESEED,0);

GO


--Inserim dades inicials de les taules

INSERT INTO UsuGestio VALUES ('Administrador','Administrador');
INSERT INTO UsuGestio VALUES ('Administratiu','Administratiu');
INSERT INTO UsuGestio VALUES ('Tecnic','Tecnic');

INSERT INTO TipusUsuari VALUES ('PL','Plantilla');
INSERT INTO TipusUsuari VALUES ('TM','Temporal');
INSERT INTO TipusUsuari VALUES ('EX','Extern');

INSERT INTO FamReferencia VALUES ('Ordinador','Ordinador','PC002/18',1,0,0,0,0);
INSERT INTO FamReferencia VALUES ('Monitor','Monitor','MN002/18',1,0,0,0,0);
INSERT INTO FamReferencia VALUES ('Switch','Switch','SW001/18',0,0,1,0,0);
INSERT INTO FamReferencia VALUES ('Cable','Cable','CB000/18',0,0,0,0,1);
INSERT INTO FamReferencia VALUES ('Teclat','Teclat','TC002/18',1,0,0,1,0);
INSERT INTO FamReferencia VALUES ('Ratoli','Ratoli','RT002/18',0,0,0,1,0);
INSERT INTO FamReferencia VALUES ('SO','Sistema Operatiu','SO001/18',0,0,0,0,0);
INSERT INTO FamReferencia VALUES ('Office','Office','OF001/18',0,0,0,0,0);

INSERT INTO Edifici VALUES ('AJ','Ajuntament','Plaça Figueres,8','Roda');
INSERT INTO Edifici VALUES ('CC','Casa de Cultura','Carrer Major, s/n','Roda');

INSERT INTO Planta VALUES ('AJ','A','0');
INSERT INTO Planta VALUES ('AJ','B','1');
INSERT INTO Planta VALUES ('AJ','C','2');
INSERT INTO Planta VALUES ('CC','A','-1');
INSERT INTO Planta VALUES ('CC','B','0');

INSERT INTO Finestreta VALUES ('AJ','A','20',NULL,NULL,NULL,NULL);
INSERT INTO Finestreta VALUES ('AJ','A','21',NULL,NULL,NULL,NULL);
INSERT INTO Finestreta VALUES ('AJ','A','22',NULL,NULL,NULL,NULL);
INSERT INTO Finestreta VALUES ('CC','A','20',NULL,NULL,NULL,NULL);
INSERT INTO Finestreta VALUES ('CC','A','21',NULL,NULL,NULL,NULL);
INSERT INTO Finestreta VALUES ('CC','A','22',NULL,NULL,NULL,NULL);

INSERT INTO Departament VALUES ('PR','Promocio Economica','C','AJ');
INSERT INTO Departament VALUES ('TIC','TIC','A','CC');
INSERT INTO Departament VALUES ('EN','Ensenyament','B','AJ');
INSERT INTO Departament VALUES ('ST','Serveis Tecnics','B','AJ');

INSERT INTO Proveidor VALUES ('Icono','Icono','Antonio Machin','12345678C','','');
INSERT INTO Proveidor VALUES ('Inford','Infordisa','Infordisa SL','77896452D','','');

INSERT INTO Departament VALUES ('TR','Tresoreria','A','AJ');
INSERT INTO Departament VALUES ('CT','Cultura','B','CC');

INSERT INTO Ubicacio VALUES ('EA01','Estanteria A, Prestatge 01');
INSERT INTO Ubicacio VALUES ('EB01','Estanteria B, Prestatge 01');
INSERT INTO Ubicacio VALUES ('AA01','Armari A, Prestatge 01');
INSERT INTO Ubicacio VALUES ('AA02','Armari A, Prestatge 02');
INSERT INTO Ubicacio VALUES ('CA02','Arxivador A, Calaix 02');

INSERT INTO Referencia VALUES
('PC001/18','125D125','Ordinador','Ordinador','Clonic','PK78','','Pendent','45AD4556','','','CA02',NULL,'','2017-12-01','','2019-12-01',0,NULL);
INSERT INTO Referencia VALUES
('PC002/18','125D256','Ordinador','Ordinador','Clonic','PK78','','Pendent','45AD4585','','','CA02',NULL,'','2017-12-01','','2019-12-01',0,NULL);
INSERT INTO Referencia VALUES
('MN001/18','S125863T','Monitor','Monitor 24','BQ','LAT24','','Pendent','','','',NULL,NULL,'','2017-12-01','','2018-12-01',0,24);
INSERT INTO Referencia VALUES
('MN002/18','S1258256','Monitor','Monitor 24','BQ','LAT24','','Pendent','','','',NULL,NULL,'','2017-12-01','','2018-12-01',0,24);
INSERT INTO Referencia VALUES
('TC001/18','12585863T','Teclat','Teclat','Cherry','M100','','Pendent','','','',NULL,NULL,'','2017-12-01','','2018-12-01',0,NULL);
INSERT INTO Referencia VALUES
('TC002/18','95885863F','Teclat','Teclat','Cherry','M100','','Pendent','','','',NULL,NULL,'','2017-12-01','','2018-12-01',0,NULL);
INSERT INTO Referencia VALUES
('RT001/18','12585DJ89','Ratoli','Ratoli','Logitech','S89-25','','Pendent','','','',NULL,NULL,'','2017-12-01','','2018-12-01',0,NULL);
INSERT INTO Referencia VALUES
('RT002/18','125K8I789','Ratoli','Ratoli','Logitech','S89-25','','Pendent','','','',NULL,NULL,'','2017-12-01','','2018-12-01',0,NULL);
INSERT INTO Referencia VALUES
('SO001/18','ADCFR-ADCFR-ACDEG-ASECF','SO','Windows 10','Microsoft','W10','','Actiu','','','',NULL,NULL,'','2017-12-01','',NULL,1,NULL);
INSERT INTO Referencia VALUES
('OF001/18','ADCFR-KLONM-ACDEG-ASECF','Office','Office Home 2016','Microsoft','OH2016','','Actiu','','','',NULL,NULL,'','2017-12-01','',NULL,1,NULL);
INSERT INTO Referencia VALUES
('SW001/18','IO9J97253','Switch','Switch HP 1820-24','HP','1820-24','J995L','Actiu','','','',NULL,NULL,'','2017-12-01','','2017-12-01',1,NULL);
INSERT INTO Referencia VALUES
('CB001/18','','Cable','Cable Cat.6 3mt','','','','Actiu','','','',NULL,NULL,'','','',NULL,10,NULL);


INSERT INTO Ordinador VALUES ('PC001/18','i5 7500',8,500,'',1,'');
INSERT INTO Ordinador VALUES ('PC002/18','i5 7500',8,1000,'',1,'');

INSERT INTO BoqSwitch VALUES ('SW001/18','A',1,1,'RJ45');
INSERT INTO BoqSwitch VALUES ('SW001/18','A',2,1,'RJ45');
INSERT INTO BoqSwitch VALUES ('SW001/18','A',3,1,'RJ45');
INSERT INTO BoqSwitch VALUES ('SW001/18','A',4,1,'RJ45');
INSERT INTO BoqSwitch VALUES ('SW001/18','A',5,1,'RJ45');
INSERT INTO BoqSwitch VALUES ('SW001/18','A',6,1,'RJ45');
INSERT INTO BoqSwitch VALUES ('SW001/18','A',7,1,'RJ45');
INSERT INTO BoqSwitch VALUES ('SW001/18','A',8,1,'RJ45');

INSERT INTO Usuari VALUES
('gomezmj','Jordi','Gomez Martinez','25698745L','2010-01-02',NULL,'Actiu','Cap de Promocio','',NULL,NULL,'PL','PR',NULL);
INSERT INTO Usuari VALUES
('masci','Irene','Mas Castro','33658745K','2011-05-06',NULL,'Actiu','Cap de TIC','',NULL,NULL,'PL','TIC',NULL);
INSERT INTO Usuari VALUES
('vilasm','Maria','Vila Sole','77589214O','2012-10-06',NULL,'Actiu','Administrativa','',NULL,NULL,'PL','EN',NULL);

INSERT INTO EntMaterial VALUES
('2016-01-04','A2425','F2425','2016-01-04','E20161354','2016-01-04','Icono');
INSERT INTO LinEntMat VALUES
(1,'PC001/18',1,435,'Factura',12,'2017-01-04');
INSERT INTO LinEntMat VALUES
(1,'TC001/18',1,15,'Factura',12,'2017-01-04');
INSERT INTO LinEntMat VALUES
(1,'MN001/18',1,70,'Factura',12,'2017-01-04');
INSERT INTO EntMaterial VALUES
('2016-01-04','A2425','F2425','2016-01-04','E20161354','2016-01-04','Icono');
INSERT INTO LinEntMat VALUES
(2,'TC002/18',1,15,'Factura',12,'2017-01-04');

GO








