Špica - Time&Space

Špica - Time&Space

Špica - Time&Space

Podesiti aplikaciju Space&Time sa preuzetim komponentama koje predloži instalacija

• Postaviti bazu podataka koju koristi aplikacija (preporučljivo sa imenom TSSPICA)
• Napraviti novi 'Login' za  bazu TSSPICA (preporučujemo da je kreirate putem aplikacije Enterprise Manager->New Login;)
• Pomoću Query Analyzer 'zakačimo' je na bazu koju koristi aplikacija Pantheon (npr. : DataLab) i uradimo sljedeće postupke:

U kodu koristimo znakove (promjenjive) koje je pri instalaciji potrebno zamjeniti odgovarajućim imenima: #SERVER# zamjeniti sa imenom servera na kojem je aktivna Time&Space #SPICA# zamjeniti sa imenom baze koju koristi aplikacija Time&Space

Prvo obrišemo moguće postojeće triggere

drop trigger dbo.pHR_Occup2TSUsers
drop trigger dbo.pHR_Prsn2TSUsers_Delete
drop trigger dbo.pHR_Prsn2TSUsers_Insert
drop trigger dbo.pHR_Prsn2TSUsers_Update
drop trigger dbo.pHR_Param2TSUsers

 Prvi trige prilokom mijenjanja naziva profesije u pantheonu mijenja naziv u Time&Spaceu.

create trigger [dbo].[pHR_Occup2TSUsers] on tHR_SetOccup
for update as

set nocount on

set xact_abort on

if Update(acName)
begin
update #STREZNIK#.[#tsspica#].[dbo].USERS
set Occupation = (select acName from vHR_SetOccup where acOccup = (select acOccup from inserted))
where ID in (select acRegNo collate SQL_Slovenian_CP1250_CI_AS from vHR_Prsn where acOccup = (select acOccup from deleted))
end

set xact_abort off

Drugi okidač, prilikom brisanja radnika u Pantheonu, takođe je briše radnika u Time & Space.

create trigger [dbo].[pHR_Prsn2TSUsers_Delete] on tHR_Prsn
for delete as

set nocount on

set xact_abort on

delete #STREZNIK#.[#tsspica#].[dbo].USERS
from #STREZNIK#.[#tsspica#].[dbo].USERS, Deleted
where Id = Deleted.acRegNo collate SQL_Slovenian_CP1250_CI_AS 

set xact_abort off

Treći okidač, kod unosa novog radnika u Pantheon, takođe unosi radnika u Time & Space.

create trigger [dbo].[pHR_Prsn2TSUsers_Insert] on tHR_Prsn
for insert as

set nocount on

set xact_abort on

-- Host: 1 - Zaposlen 2 - Obiskovalec

insert into #STREZNIK#.[#tsspica#].[dbo].USERS (HOST, ID) 
select 1, (select acRegNo collate SQL_Slovenian_CP1250_CI_AS from inserted) 

set xact_abort off

 Četvrti triger pri promjeni podataka radnika (prezime, ime, adresa, telefonski broj, datum rođenja, odjel, e-mail) u PANTHEON-u promijeniti ovu vrijednost u Time & Space.

create trigger [dbo].[pHR_Prsn2TSUsers_Update] on tHR_Prsn
for update as

set xact_abort on

set nocount on

if Update(acSurname) or Update(acName) or Update(acStreet) or Update(acCity) or Update(acCountry)
or Update(acPhone) or Update(adDateBirth) or Update(acDept) or Update(acEmail)
begin
update #STREZNIK#.[#tsspica#].[dbo].USERS
set LASTNAME = RTrim(acSurname),
FIRSTNAME = RTrim(acName),
ADDRESS = acStreet,
CITY = acCity,
STATE = acCountry,
PHONE = acPhone,
BIRTH = adDateBirth,
EMAIL = Inserted.acEmail
from Inserted
where ID = Inserted.acRegNo collate SQL_Slovenian_CP1250_CI_AS
end

if Update(acPicture)
begin
update #STREZNIK#.[#tsspica#].[dbo].USERS
set PICTURE = vHR_Prsn.acPicture 
from #STREZNIK#.[#tsspica#].[dbo].USERS, vHR_Prsn, inserted, deleted
where vHR_Prsn.acRegNo = inserted.acRegNo 
and ID = deleted.acRegNo collate SQL_Slovenian_CP1250_CI_AS
and vHR_Prsn.acWorker = inserted.acWorker 
and vHR_Prsn.acWorker = deleted.acWorker 
end

if Update(acOccup)
begin
update #STREZNIK#.[#tsspica#].[dbo].USERS
set OCCUPATION = vHR_SetOccup.acName 
from vHR_SetOccup, Inserted, Deleted
where vHR_SetOccup.acOccup = Inserted.acOccup 
and ID = Deleted.acRegNo collate SQL_Slovenian_CP1250_CI_AS
and Inserted.acWorker = Deleted.acWorker 
end

if Update(acRegNo)
begin
update #STREZNIK#.[#tsspica#].[dbo].USERS
set ID = Inserted.acRegNo 
from Inserted, Deleted
where ID = Deleted.acRegNo collate SQL_Slovenian_CP1250_CI_AS
and Inserted.acWorker = Deleted.acWorker 
end

set xact_abort off

 Peti okidač mijenja ime u Time & Space kada se mijenja ime našeg preduzeća u Pantheonu.

create trigger [dbo].[pHR_Param2TSUsers] on tPA_SysParam
for update as

set nocount on

set xact_abort on

if Update(acOurCompany) 
begin
update #STREZNIK#.[#tsspica#].[dbo].USERS
set COMPANY = (select acOurCompany from Inserted)
where COMPANY = (select acOurCompany from Deleted) collate SQL_Slovenian_CP1250_CI_AS
end

set xact_abort off

 Šesti okidač, kada se menja Odjel zaposlenih u Pantheonu, takođe menja Odjel na Time & Space.

create trigger [dbo].[pHR_Prsnjob2TSUsers_UpdIns] on tHR_Prsnjob

for update, insert as

set xact_abort on

set nocount on

if Update(acDept) or Update(acActive)

begin

update #STREZNIK#.[tsspica].[dbo].USERS

set DEPARTMENT = I.acDept

from Inserted I

join vHR_Prsn D on D.acWorker = I.acWorker

where I.acActive = 'T'

and ID = D.acRegNo collate SQL_Slovenian_CP1250_CI_AS

end

Prvi SQL koji je dokumentiran / neaktivan (aktiviramo ako uklonimo prva dva znaka -) će izbrisati postojeće radnike u Time & Space. UPOZORENJE !!! U slučaju da pokrenete ovu rečenicu, svi zaposlenici će biti izbrisani i ove informacije neće biti moguće vratiti.

--delete from #STREZNIK#.[#tsspica#].[dbo].USERS

Drugi SQL unosi korisnike iz Pantheona u Time & Space. UPOZORENJE !!! Svaki put kada se ova stavka počne, svi radnici iz Pantheona će biti prebačeni u Time & Space, tako da kada se pokrenu više puta, radnici će biti uneseni nekoliko puta.

insert into #STREZNIK#.[#tsspica#].[dbo].USERS (Lastname, Firstname, Address, City, State, Phone, Fax, ID, Birth,
Occupation, Bdgno, Company, Department, Subdepartment, Host, Picture, EMail, Pin, BdgChangedOn, Other)
select RTrim(cast(acSurname as VarChar(40))) as LastName, 
RTrim(cast(acName as VarChar(25))) as FirstName,
cast(acStreet as VarChar(50)) as Address, 
cast(acCity as VarChar(50)) as City, 
cast(acCountry as VarChar(50)) as State, 
cast(acPhone as VarChar(15)) as Phone, 
cast('' as VarChar(15)) as Fax, 
cast(acRegNo as VarChar(13)) as ID, 
adDateBirth as Birth,
cast((select acName from vHR_SetOccup where acOccup = D.acOccup) as VarChar(50)) as Occupation,
null as Bdgno,
cast((select acOurCompany from tPA_SysParam) as VarChar(50)) as Company,
cast(acDept as VarChar(50)) as Department, 
cast('' as VarChar(50)) as SubDepartment,
cast(1 as SmallInt) as Host, 
acPicture as Picture, 
cast(acEmail as VarChar(30)) as EMail,
cast('' as VarChar(6)) as Pin, 
null as BdgChangedOn,
null as Other
from vHR_Prsn D

Nakon izvedenih procedura treba prvi put pokrenuti aplikaciju Time&Space kada se unosi kreirano korisničko ime (login): TSSPICA i odgovarajuća lozinka.