Špica - Time and Space

Špica - Time&Space

Namestiti aplikacijo Space&Time s privzetimi komponentami, ki jih ponudi namestitveni program,

• Namestiti predpripravljeno podatkovno bazo, ki jo omenjena aplikaccija uporablja(priporočljivo z imenom TSSPICA),
• Kreirati novi 'Login' s privzeto podatkovno bazo TSSPICA (priporočljivo kreiranje le-tega kar preko aplikacije Enterprise Manager->New Login;)
• S pomočjo Quary Analyzer se 'povežemo' na podatkovno bazo, ki jo uporablja aplikacija Pantheon(npr.: Datalab) in po vrsti zaganjamo postopke pripravljene za prenos podatkov oz. zaženemo sledečo proceduro v kateri so omenjeni SQL postopki združeni :

V kodi uporabljamo sledeče oznake (spremenljivke), ki jih je potrebno ob instalaciji zamenjati z ustreznimi imeni: #STREZNIK# zamenjati z imenom strežnika, na katerem teče podatkovna baza za Time&Space #tsspica# zamenjati z imenom baze ki jo uporablja aplikacija Time&Space

Najprej pobrišemo morebitne obstoječe triggerje (sprožilce)

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 trigger ob spremembi naziva poklica v Pantheonu spremeni naziv tudi v Time&Space.

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 trigger ob brisanju delavca v Pantheonu pobriše delavca tudi v 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

 Tretji trigger ob vnosu novega delavca v Pantheonu vnese delavca tudi v 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

Četrti trigger ob spremembi podatkov o delavcu (priimek, ime, naslov, telefon, datum rojstva, oddelek, e-pošta) v Pantheonu spremeni te vrednosti tudi v 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 trigger ob spremembi naziva našega podjetja v Pantheonu spremeni naziv tudi v Time&Space.

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 trigger ob spremembi oddelka zaposlenega v Pantheonu spremeni oddelek tudi v 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, ki je dokumentiran/neaktiven (aktiviramo, če odstranimo prva dva znaka --) pobriše obstoječe delavci v Time& Space. OPOZORILO!!! V primeru, da poženete ta stavek vam bojo pobrisani vsi delavci in teh podatkov ne bo možno obnoviti.

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

Drugi SQL vnese uporabnike iz Pantheona v Time&Space. OPOZORILO!!! Pri vsakem zagonu tega stavka se prenesejo vsi delavci iz Pantheona v Time&Space, torej pri večkratnem zagonu bodo delavci večkrat vnešeni.

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

Po izvedenih procedurah sledi prvi zagon aplikacije Time &Space pri katerem je potrebno vnesti prej kreirano uporabniško ime (login): TSSPICA ter pripadajoče geslo;